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:

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.

Case Insensitive

The SUMIFS function is not a case-sensitive function. If you write t-shirt / california, it will also return the result.

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.

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.

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.

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.

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.

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

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