Excel SUMIFS Multiple Criteria (All Applications)

In this article, you will learn how to use Excel SUMIFS function with multiple criteria. Here, you will see the application of AND, OR logic within the SUMIFS function. Then how you can use the comparison operator in the SUMIFS function. Also, you will get the usage of SUMIFS in Excel tables.

Furthermore, we will discuss the common problems of the SUMIFS function. Moreover, from this article, you will get an idea of the usage of the SUMIF function.

The SUMIFS function is very important in the case of calculating a summation based on conditions.

Basically, the SUMIFS function helps you to add numbers that fulfill more than one condition. It makes calculations easy and lets you analyze data efficiently. You can use multiple criteria to get the exact results you need. It’s a fast and useful tool for working with data in Excel.

Download Practice Workbook

You are recommended to download the Excel file and practice along with it.

Using SUMIFS Function with Multiple Criteria.xlsx

Excel SUMIFS Multiple Criteria: 5 Suitable Examples

1. Using AND Logic in SUMIFS with Multiple Criteria

We want to find out the total sales of T-shirts from California states.
In the H10 cell, enter the following formula.

=SUMIFS(E5:E14,B5:B14,H7,C5:C14,H8)

Here, E5:E14 is the sum range. B5:B14 and C5:C14 are the criteria range 1, and criteria range 2 respectively. H7 and H8 cell values are criteria 1, and criteria 2.

Case Insensitive

The SUMIFS function is not a case-sensitive function. In the case of criteria, if you write t-shirt / california, that will return the result too.

Note: When you have a large dataset, then press CTRL+SHIFT+down key to select the whole range.

2. Applying OR Logic in SUMIFS with Multiple Criteria in One Column

Now we will calculate the sales of products either shirts or t-shirts.
Go to a blank cell (E16) and use the following formula.

=SUM(SUMIFS(E5:E14,C5:C14,{"Shirt","T-shirt"}))

Press ENTER to get the result.

Formula Breakdown

Here, in the SUMIFS function, E5:E14 is the Sum range. C5:C14 is the criteria range. Criteria are shirts or t-shirts.
So, the SUMIFS function will return the sales of shirts and t-shirts.
- Output: SUMIFS(E5:E14,C5:C14,{“Shirt”,”T-shirt”}) becomes {19250,23950}.
Then, the SUM function will add the above result.
- Output: 43,200

Note: As the criteria are text values you must use inverted commas. Again, both of them are in the same column so you can use the 2nd bracket.

3. Combing SUMPRODUCT and SUMIFS Functions for Calculating Total Sales

Here, we will find the total sales of shirts or t-shirts from California state.
Select a new cell and insert the following formula.

=SUMPRODUCT(SUMIFS(E5:E14,B5:B14,H6,C5:C14,{"Shirt";"T-shirt"}))

In this formula, the SUMIFS function will return the total sales of shirts or t-shirts from California state. But the result will be located in individual cells for the shirt and t-shirt. To sum up the result, we used the SUMPRODUCT function.

4. Applying SUMIFS Function with Comparison Operator

We will calculate the total sales of products whose sales quantity is between 300 to 600.
So, write the formula given below in the H10 cell.

=SUMIFS(E5:E14,D5:D14,">300",D5:D14,"<=600")

Here, E5:E14 is the sum range. D5:D14 is the criteria range for both criteria. Criteria are mentioned in inverted commas.

Note: In the case of the SUMIFS function, while working with a comparison operator for criteria, you must use the inverted comma.

5. Applying SUMIFS in Excel Table

You can use the SUMIFS function in the same way in the Excel table too.
We have an Excel table. Now, we will find the total sales of t-shift from California, which quantity is 785.
In the H11 cell, we have used the following formula.

=SUMIFS(Table1[Sales],Table1[States],H7,Table1[Product],H8,Table1[Quantity],H9)

Here, instead of choosing the range, you can use the column headers.

Note: You can select the range and press Ctrl+T to convert the range to a table like this. Alternatively, you can also go to the Insert tab and select Table too.

Common Problems While Using SUMIFS Function in Excel

When you use a text value, or numeric value with a comparison operator, then you must use the inverted commas (“”). Without inverted commas, the SUMIFS function will return 0 as a result.

Blank cells in the criteria range are treated as an empty string (“”), so they can match with empty criteria. Be cautious when dealing with blank cells as criteria, as they might lead to unexpected results.

How to Use SUMIF Function with Multiple Criteria in Excel

The SUMIF function considers one criterion. So, if you need to use multiple criteria then you must use the SUMIF function multiple times.
Below, you can see an example of how to use the SUMIF function for multiple criteria.

=SUM(SUMIF(C5:C14,H7,E5:E14),SUMIF(C5:C14,H8,E5:E14))

Limitations When Using Multiple Criteria with Excel SUMIFS Function

Here, the number of criteria ranges must be equal to the number of criteria.
The criteria range and sum range (the range you want to sum) must have the same size. In other words, both ranges should have an equal number of rows and columns. Otherwise, it will show an #VALUE! error.
Another one is the data types of the criteria in the criteria ranges must match the data types of the values you are comparing them with.
You can use wildcards in text criteria to represent partial matches, but they may not work with numeric or date criteria.
SUMIFS is generally efficient, when dealing with very large datasets, using multiple SUMIF or SUMIFS functions might cause performance issues.

Frequently Asked Questions

1. Can I use SUMIFS with multiple criteria on different worksheets or workbooks?

Yes, you can use the SUMIFS function within multiple worksheets. But while using the different workbooks, you need to keep opening both workbooks.

2. What happens if no matching criteria are found in the SUMIFS formula?

When there are no matching criteria, the SUMIFS function will return 0. Actually, this SUMIFS function looks for cells that meet all the specified criteria. If there are no cells that satisfy all the conditions, the function won’t find any values to sum, and the output will be zero.

3. How do I handle empty or blank cells while using multiple criteria in SUMIFS?

You can add a criterion with the same range as “<>” which will avoid the blank cells.

Conclusion

That’s it for today. Hopefully, you understand how to use the Excel SUMIFS function with multiple criteria. Here, we tried to show you different scenarios and based on that we explained the usage of the SUMIFS function. With this function, you can create dynamic reports. Also, you can easily make conclusions from your data through this.

If you face any problem regarding this function, feel free to comment below. We will try our best to solve your problem.

Excel SUMIFS Multiple Criteria: Knowledge Hub

<< Go Back to Excel SUMIFS Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!