Excel SUMIFS Multiple Criteria (All Applications)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel SUMIFS Multiple Criteria

Download Practice Workbook

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

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.

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.

Using AND Logic in SUMIFS with Multiple Criteria

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.

SUMIFS Function is a Case-insensitive function

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.
  • 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

Applying OR Logic in SUMIFS in One Column

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.

Combing SUMPRODUCT and SUMIFS Function for Calculating Total Sales

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.

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

Applying SUMIFS Function with Comparison Operator

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

Read More: Use SUMIFS Function with Wildcard in Excel

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.

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

Applying SUMIFS in Excel Table

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.

Read More: Use SUMIFS with Multiple Criteria in the Same Column

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.

Common Problems While Using SUMIFS Function in Excel

  • 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.

Read More: [Fixed]: SUMIFS Not Working with Multiple Criteria

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.

How to Use SUMIF with Multiple Criteria

Read More: SUMIFS with Multiple Criteria Along Column and Row in Excel

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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF