How to Use SUMIFS Function in Excel with Multiple Criteria

If you want to sum values that meet multiple criteria then you can use the SUMIFS function. The SUMIFS function can sum cells that meet multiple criteria. If you use two different criteria, then the SUMIFS function will sum the cells that meet both of the criteria. This function generally works with AND logic. The focus of this article is to explain how you can use the SUMIFS function in Excel with multiple criteria.

Download Practice Workbook

You can download the practice workbook for the SUMIFS function in Excel with multiple criteria from the link below.

8 Suitable Examples to Use SUMIFS Function in Excel with Multiple Criteria

Here, I have taken the following dataset to explain this article. This dataset contains the State, Product, and Sales columns. I will use this dataset to explain how you can use the SUMIFS function in Excel with multiple criteria. I will show 8 different examples to illustrate the functionality of the SUMIFS function with multiple criteria more clearly.

1. SUMIFS Function with Multiple Criteria

In this first example, I will show you the basic use of the SUMIFS function with multiple criteria. Here, I will calculate the Total Sales for the Mobiles that are sold from Texas. I will use these 2 criteria in the SUMIFS function and the function will only add those cells that meet both criteria.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want the Total Sales. Here, I selected cell G8.
• Secondly, in cell G8 write the following formula.

Here, in the SUMIFS function, I selected cell range D5:D14 as sum_range, B5:B14 as criteria_range1, G5 as criteria1, C5:C14 as criteria_range2, and G6 as criteria2. Now, the formula will sum the values from the cell range D5:D14 that meet both criteria1 and criteria2.

• Thirdly, press ENTER and you will get your desired result.

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

2. Using Comparison Operator in SUMIFS Function with Multiple Criteria

In this example, I will show you how you can use the Comparison Operator in the SUMIFS function in Excel with multiple criteria. In the following dataset, you can see that I want to calculate the Total Sales for Mobile whose Sales are more than or equal to $800.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want to calculate the Total Sales. Here, I selected cell G8.
• Secondly, in cell G8 write the following formula.

Here, in the SUMIFS function, I selected cell range D5:D14 as sum_range, C5:C14 as criteria_range1, G5 as criteria1, D5:D14 as criteria_range2, and >=800 as criteria2. Now, the formula will sum the values from the cell range D5:D14 that meet both criteria1 and criteria2.

• Thirdly, press ENTER to get the Total Sales.

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

3. Multiple Criteria for Dates in Excel

In this example, I will use the SUMIFS function with multiple criteria for dates in Excel. Suppose you have a dataset that contains Date, Product, and Sales. Now, you want to calculate the Total Sales for a specific Product in the last 1 week. You can do it easily by using the SUMIFS function.

Let’s see how you can do it.

Steps:

• First, select the cell where you want to calculate the Total Sales.
• Then, in that selected cell write the following formula.

• Finally, press ENTER to get the Total Sales.

Read More: SUMIFS Sum Range Multiple Columns in Excel(6 Easy Methods)

4. Use of SUMIFS Function in Excel with Blank and Non-Blank Cells

In this example, I will show you how you can use the SUMIFS function in Excel with multiple criteria for bank and non-blank cells. For this example, I have taken the following dataset. This dataset contains Branch and Projects that are assigned to some Members and the Start Date and the End Date of the Projects are given. Suppose you want to calculate the number of Total Members working on Running Projects in Branch 1. Now, I will show you how you can do it by using the SUMIFS function.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want to calculate the Total Members. Here, I selected cell C20.
• Secondly, in cell C20 write the following formula.

• Thirdly, press ENTER to get the Total Members.

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

Similar Readings

• Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria
• Excel SUMIFS with Multiple Vertical and Horizontal Criteria
• How to Use VBA Sumifs with Multiple Criteria in Same Column
• SUMIFS with INDEX-MATCH Formula Including Multiple Criteria

5. SUMIFS Function in Excel with Multiple OR Criteria

The SUMIFS function generally works with AND logic. But you can also use this function for OR logic. Here, I will explain how you can use the SUMIFS function with multiple OR criteria. Suppose, you want to calculate the Total Sales of Headphones in Texas or Florida. I will show you how you can do that.

Let’s see the steps.

Steps:

• Firstly, select the cells where you want to calculate the Total Sales. Here, I selected cell G8.
• Secondly, in cell G8 write the following formula.

• Finally, press ENTER to get the result.

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

6. Applying SUMIFS & SUM Functions for Multiple Criteria

In this example, I will use the SUMIFS function and the SUM function together for multiple OR criteria in Excel. The dataset of this example is the same as the previous example. Here, I will solve the same problem by using a different formula. I will show you how you can calculate the Total Sales of Headphones in Texas or Florida.

Let’s see the steps.

Steps:

• First, select the cell where you want the Total Sales. Here, I selected cell G8.
• Secondly, in cell G8 write the following formula.

• Thirdly, press ENTER to get the result. If you are using an older version of Excel than Excel 2019 then press CTRL+SHIFT+ENTER to get the result.

Read More: SUMIFS with Multiple Criteria in the Same Column (5 Ways)

7. Using Named Range for Dynamic Criteria in SUMIFS Function in Excel

In this example, I will show you how you can use Named Range in the SUMIFS function with multiple criteria in Excel. To explain this example, I have taken the following dataset. Here, I will calculate the Total Sales for the State of Texas or Florida.

Let’s see the steps.

Steps:

• Firstly, select the cell range that you want to use as the criteria.
• Secondly, write a name for the selected range. Here, I named mine as Multiple_Criteria.

• After that, select the cell where you want your Total Sales. Here, I selected cell G9.
• Next, in cell G9 write the following formula.

Here, in the SUMIFS function, I selected cell range D5:D14 as sum_range, B5:B14 as criteria_range1, and Multiple_Criteria as criteria1. Here, Multiple_Criteria is the named range. The SUMIFS function will look for every value in the named range and return the sum for these values. And then, the SUM function will return the summation of these values as Total Sales.

• Finally, press ENTER to get the result. If you are using an older version of Excel than Excel 2019 then press CTRL+SHIFT+ENTER to get the result.

Read More: SUMIFS Multiple Columns in Excel (5 Types of Application)

8. Use of Wildcard Character in SUMIFS Function with Multiple Criteria

In this example, 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 you have a dataset containing Sales Person, Product, and Sales column. And you want to calculate the Total Sales of Mobile from Sales Person whose names contain the letter “N”.

Let’s see the steps.

Steps:

• First, select the cells where you want to calculate the Total Sales. Here, I selected cell G8.
• Then, in cell G8 write the following formula.

Here, in the 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.

• After that, press ENTER to get the result.

Read More: SUMIFS with Wildcard in Excel (+ Alternative Formulas)

Things to Remember

• Generally, the SUMIFS function works with AND logic. But, you can get results for OR logic with the SUMIFS function easily by following the methods from examples 5, 6, or 7.
• If you are using an older version of Excel than Excel 2019 then you will have to press CTRL+SHIFT+ENTER to get results from an Array formula.

Practice Section

Here, I have provided a practice sheet for you to practice the explained examples of using the SUMIFS function in Excel for multiple criteria.

Conclusion

To conclude, I tried to explain the uses of the SUMIFS function in Excel with multiple criteria in this article. Here, I explain 8 different examples. I hope this article was helpful for you. For more articles stay connected with ExcelDemy. If you have any questions, feel free to let me know in the comment section below.

Related Articles

• How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows
• Exclude Multiple Criteria in Same Column with SUMIFS Function
• How to Use SUMIFS When Cells Are Not Equal to Multiple Text
• SUMIFS with Multiple Criteria Along Column and Row in Excel
• SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)