If you want to sum the values of some cells when they meet one or more criteria, then **the SUMIFS function **is an excellent choice. There are plenty of uses for the function in **Excel**. You can use it with many other functions also. In this article, I will show you 8 effective applications of the **Excel SUMIFS **function in multiple columns.

## Download Practice Workbook

You can download the practice workbook from here for practicing.

## Introduction to Excel SUMIFS Function

**SUMIFS** function is an Excel function that adds all values based on multiple criteria. This function was introduced in **2007**. Since its initiation, it’s become popular day by day.

**Syntax**

The syntax of the function is

**=SUMIFS(sum_range, range1, criteria 1, [range2]), [criteria2],…)**

**Arguments**

There are the following arguments in the function.

**sum_range** – The range to sum.

**range1** – The first range as criteria.

**criteria1*** –* The criteria for **range1**.

**range2** – [optional] The second range as **criteria**.

**criteria2** *–* The criteria for **range2**.

We can add more criteria ranges and criteria as arguments.

## 8 Effective Applications of SUMIFS Function with Multiple Columns in Excel

The **SUMIFS **function can be used in various ways. Here, I will show 8 effective applications of the** SUMIFS **function in multiple columns.

### 1. Use SUMIFS in Multiple Columns with Single Criteria

The **SUMIFS** function can be used to sum values that meet a single criterion. I will explain the procedure to do that with the following example. In the dataset, Some popular **Name of Sites** is given along with the **Number of Visits**. Besides, the **Platform** which is used and also the **Date** of counting the number of visits are provided. Let’s follow the steps given below to sum the number of visits for each site.

- First, select cell
**H5**.

- Next, write the following formula in the cell and hit
**Enter**.

`=SUMIFS($E$5:$E$15,$B$5:$B$15,G5)`

- Further, use the
**Fill Handle**to copy the formula to the cells below. - Hurrah! We have calculated the
**Total Visits**for each site in the dataset.

**E5:E15**is the cell range for the number of visits,

**B5:B15**is for the name of sites, and

**G5**is the cell of a site namely exceldemy.com.

**Read More:** **[Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)**

### 2. Apply SUMIFS with Multiple Criteria in Multiple Columns

Now, I will show the application of the **SUMIFS **function with multiple criteria in multiple columns. I will use the dataset of **Application 1** for demonstrating the procedure. Let’s follow the given steps.

- Firstly, select cell
**I5**and write the following formula there.

`=SUMIFS($E$5:$E$15,$B$5:$B$15,G5,$C$5:$C$15,H5)`

- After that, hit
**Enter**. - Further, use the
**AutoFill**option to copy the formula in the cells below.

**E5:E15**(sum_range) is the cell range for the number of visits,

**B5:B15**(criteria range 1) represents the names of sites,

**G5**(criteria1) is the cell of a site

**exceldemy.com**,

**C5:C15**(criteria range2) is for the platform, and

**H5**(criteria2) is the name of the platform

**web**.

**Read More:** **SUMIFS Multiple Criteria Different Columns (6 Effective Ways)**

### 3. Insert Excel SUMIFS in Multiple Columns with OR Logic

The **SUMIFS **function can be used with **OR **logic to add the values which meet multiple criteria. In the dataset, there is the **Name of Sites**, and **Number of Visits **with their respective **Date**. Suppose we want the total number of visits for three sites for the month of **June**. Let’s follow the steps given below.

- Firstly, select cell
**H9**and write the following formula there.

`=SUMIFS(E5:E15,B5:B15,H6,D5:'SUMIFS_OR Logic'!D15,">=6/1/2021",D5:D15,"<=6/30/2021")+SUMIFS(E5:E15,B5:B15,H7,D5:D15,">=6/1/2021",D5:D15,"<=6/30/2021")+SUMIFS(E5:E15,B5:B15,H8,D5:D15,">=6/1/2021",D5:D15,"<=6/30/2021")`

- Then, press
**Enter**. - Bravo! we will see the total number of visits from the sites for the month of
**June**.

**Note:**Remember,

**6/1/2021**and

**6/30/2021**mean the whole month of

**June**.

🔎 **How Does the Formula Work?**

Now, let’s understand the formula.

- In the first
**SUMIFS Syntax**(for the site exceldemy.com),**E5:E15**is the cell range for the number of visits,**B5:B15**is for the name of sites,**H6**is the name of the site, and**D5:D15**is for the dates.

- Similarly, two more
**SUMIFS functions**are used and their results are added by using**OR**logic(**+**).

**Read More:** **Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)**

**Similar Readings**

**SUMIFS with Multiple Criteria in the Same Column (5 Ways)****SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)****Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria****Excel SUMIFS with Multiple Vertical and Horizontal Criteria****SUMIFS with INDEX-MATCH Formula Including Multiple Criteria**

### 4. Use AND Logic with Excel SUMIFS Function

The **SUMIFS **function can also be used with the **AND **operator. For demonstrating the procedure I have used the dataset from **Application 1**. Here, I will calculate the sum of the **Number of Visits **for **exceldemy.com** if the values are greater than **2500**. I am showing the steps for this application below.

- First, select cell
**I5**and write the following formula.

`=SUMIFS(E5:E15,B5:B15,G5,E5:E15,">=2500")`

- Next, press
**Enter**. - We will see the
**Total Visits**for**exceldemy.com**if the number of visits for a day is more than or equal to**2500**.

**E5:E15**is the cell range for the number of visits,

**B5:B15**is for the name of sites, and

**G5**is the criteria. Again,

**E5:E15**is the criteria range and

**>=2500**is the

**2nd criterion**.

**Read More:** **How to Use SUMIFS Formula with Multiple Criteria in Excel (11 Ways)**

### 5. Insert SUMIFS in Multiple Columns Having Dates (Date Range)

We can use the **SUMIFS **function to sum values between two dates. I have used the dataset from **Application 1 **to explain the use of the **SUMIFS **in multiple columns having dates. Let’s follow the steps below for that.

- Firstly, select cell
**H7**and write the following formula there.

`=SUMIFS(E5:E15,B5:B15,H6,D5:D15, ">="&H5, D5:D15, "<="&I5)`

- Then, hit
**Enter**. - Consequently, we will see the result.

**E5:E15**(sum range) is the cell range for the number of visits,

**B5:B15**(criteria range1) is for the name of sites,

**H6**(criteria1) is the name of the site,

**D5:D15**(criteria range2) is for the dates,

**H5**(criteria2) is the start date. Again,

**D5:D15**criteria range3) and

**I5**(criteria3) is the end date.

**Read More:** **How to Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)**

### 6. Apply SUMIFS Function for Empty Cells in Excel

The **SUMIFS **function can also sum values taking empty cells as criteria. In the dataset, I have taken some **Fruit **names, their **Order Date** and **Delivery Date,** and the delivered **Quantity**. Follow the steps given below to sum the **Quantity **if it was not delivered (**Delivery Date **is empty). Let’s follow the steps given below.

- Select cell
**B14**and write the formula given below.

`=SUMIFS(E5:E11,C5:C11,"<> ",D5:D11,"=")`

- Next, press
**Enter**. - Consequently, we will see the
**Quantity of Not Delivered Products**.

In the formula,

**E5:E11**is the**sum range****C5:C11**is the range of**Order Date,**and**“<> “**is the**criteria**for this range which means not equal to**Blank**.- The range of
**Delivery Date**is**D5:D11**and**“=”**is the**criteria**for this range which means equal to**Blank**. ( You can use**” ”**instead of**“=”**also)

### 7. Combine SUMIFS and SUM Functions in Multiple Columns

We can use the **SUMIFS **function and **the SUM function **together to sum values from multiple columns. For explaining this application I introduced **State, Product, **and **Sales**. We will calculate **Total Sales **if the criteria match. Let’s follow the steps given below for this application.

- First, select cell
**G8**. - Then, write the following formula there and hit
**Enter**.

`=SUM(SUMIFS(D5:D14,C5:C14,G5,B5:B14,{"Texas","Florida"}))`

- Finally, we will see the
**Total Sales**for values that match the criteria.

🔎 **How Does the Formula Work?**

- In the formula, here in the
**SUMIFS**function, an**array**was selected as**criteria**. This array contains two different values. The function will look for both of these values separately and return the sum for both.

**Output: {1300,2200}**

**SUM(SUMIFS(D5:D14,C5:C14,G5,B5:B14,{“Texas”,”Florida”}))**turns into**SUM({1300,2200})**.

- Here, the
**SUM**function will return the summation of these**2**values.

**Output: 3500**

### 8. Use Wildcard Characters in SUMIFS Function with Multiple Criteria

I will show you how you can use **Wildcard**** Character ****(~,*,?)** in the **SUMIFS** function for **multiple criteria** in Excel. For this example, I will use the **Asterisk (*)** symbol. Suppose we have a dataset containing **Sales Person**, **Product**, and **Sales **column. And we want to calculate the **Total Sales** of **Mobile** from **Sales Person** whose names contain the letter **“n”**. let’s follow the steps given below for this application of the **SUMIFS **function.

- First, select cell
**G8**and write the following formula there.

`=SUMIFS(D5:D14,C5:C14,G5,B5:B14,"*n*")`

- Then, hit
**Enter**. - Consequently, we will see the
**Total Sales**for**Sales Person**whose names have the letter (**n**).

**SUMIFS**function, I selected cell range

**D5:D14**as

**sum_range**,

**C5:C14**as

**criteria_range1**,

**G5**as

**criteria1**,

**B5:B14**as

**criteria_range2**, and

**“*n*”**as

**criteria2**. Here,

**“*n*”**means any word that contains the letter “

**n**”. Now, the formula will

**sum**the values from the cell range

**D5:D14**that meet both

**criteria1**and

**criteria2**.

## Alternative of the Uses of SUMIFS with Multiple Columns in Excel

Instead of the **SUMIFS **function, we can use **the SUMPRODUCT function **to sum values that match multiple criteria. We have used the dataset from **Application 1** to demonstrate this application. Follow the steps given below for this alternative application.

- First, select cell
**I5**and write the following formula there.

`=SUMPRODUCT(($B$5:$B$15=G5)*($C$5:$C$15=H5)*($E$5:$E$15))`

- Afterward, press
**Enter**. - Instantly, we will see the result which is the sum of values that match the criteria.

**B5:B15**is for the name of sites,

**G5**is the cell of a site namely

**exceldemy.com**,

**C5:C15**is for the platform, and

**H5**is the name of a platform (

**web**), and

**E5:E15**is the cell range for the number of visits.

## Things to Remember

- Don’t forget to use
**double quotes**(e.g. input as**“<“**). - Input the formula with
**correct logic**(e.g. don’t input**“>”**instead of**“>=”**). - Be careful about the file name, file location, and Excel extension name.

## Conclusion

The **SUMIFS **function is widely used to sum values that match certain criteria. In this article, I have shown 8 effective applications of the **SUMIFS **function. Hopefully, it will help you to understand the use of the **SUMIFS **function in various ways. If you have any queries or suggestions, please leave a comment in the comment section.

## Related Articles

**How to Use VBA Sumifs with Multiple Criteria in Same Column****Exclude Multiple Criteria in Same Column with SUMIFS Function****How to Use SUMIFS When Cells Are Not Equal to Multiple Text****SUMIFS Sum Range Multiple Columns in Excel(6 Easy Methods)****How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows****SUMIFS with Multiple Criteria Along Column and Row in Excel**