Using the Excel SUMIFS Function with Multiple Criteria - 5 Examples

This is an overview:

Excel SUMIFS Multiple Criteria

Download Practice Workbook

Download the Excel file.

Using SUMIFS Function with Multiple Criteria.xlsx

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.

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

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.

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

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.

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

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.

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

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.

Example 5 – Applying the SUMIFS in an Excel Table

Find the total sales of t-shirts in California.

In H11, use the following formula.

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

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.

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 (“”).

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:

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

How to Use SUMIF with Multiple Criteria

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!

Using the Excel SUMIFS Function with Multiple Criteria – 5 Examples