Using the Excel SUMIFS Function with Multiple Criteria – 5 Examples

With the SUMIFS function you can add numbers that fulfill more than one condition.  This is an overview:

Excel SUMIFS Multiple Criteria

Download Practice Workbook

Download the Excel file.

Example 1 – Using the AND Logic in SUMIFS with Multiple Criteria

To find the total sales of T-shirts in California:

  • In H10, enter the following formula.

E5:E14 is the sum range. B5:B14 and C5:C14 are the criteria range 1, and  2. H7 and H8 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. If you write t-shirt / california, it will also return the result.

SUMIFS Function is a Case-insensitive function

Note: When you have a large dataset, press CTRL+SHIFT+down key to select it.

Example 2 – Applying the OR Logic in SUMIFS with Multiple Criteria in One Column

To calculate the sales of either shirts or t-shirts.

  • Go to a blank cell (E16) and use the following formula.
  • Press ENTER to see the result.

Formula Breakdown

  • E5:E14 is the Sum range. C5:C14 is the criteria range. Criteria are shirts or t-shirts.
  • The SUMIFS function returns the sales of shirts and t-shirts.
    • Output: SUMIFS(E5:E14,C5:C14,{“Shirt”,”T-shirt”}) becomes {19250,23950}.
  • The SUM function adds 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.

Example 3 – Combining  the SUMPRODUCT and the SUMIFS Functions to Calculate the Total Sales

Find the total sales of shirts or t-shirts in California.

  • Select a new cell and use the following formula.

Combing SUMPRODUCT and SUMIFS Function for Calculating Total Sales

The SUMIFS function returns the total sales of shirts or t-shirts in California. The result is displayed in individual cells for shirt and t-shirt. To sum the result, use the SUMPRODUCT function.

Example 4 – Applying the SUMIFS Function with a Comparison Operator

Calculate the total sales of products whose sales quantity is between 300 to 600.

  • Enter the formula below in H10.

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

Applying SUMIFS Function with Comparison Operator

Note: The inverted comma must be used while working with a comparison operator.

Read More: Use SUMIFS Function with Wildcard in Excel

Example 5 – Applying the SUMIFS in an Excel Table

In the Excel table, find the total sales of t-shirts in California.

  • In H11, use the following formula.

Instead of choosing the range, 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. You can also go to the Insert tab and select Table.

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

Common Problems While Using the SUMIFS Function in Excel

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

Common Problems While Using SUMIFS Function in Excel

  • Blank cells in the criteria range are treated as an empty string (“”).

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

How to Use the SUMIF Function with Multiple Criteria in Excel

  • The SUMIF function considers one criterion. To use multiple criteria, use the SUMIF function multiple times, as shown below:

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

  • The number of criteria ranges must be equal to the number of criteria.
  • The criteria range and the sum range must be the same size. Otherwise, it will show a #VALUE! error.
  • 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 return partial matches, but they may not work with numeric or date criteria.

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. Keep both workbooks open.

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

It returns 0.

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 “<>” to avoid blank cells.

Excel SUMIFS Multiple Criteria: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!
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