How to Use the SUMIFS Function in Excel with Multiple Criteria

Use SUMIFS when you need Excel to add values from rows that meet every condition. Start with the range that contains the values to total, then add each criteria range and its matching condition.

=SUMIFS($D$5:$D$10,$B$5:$B$10,"West",$C$5:$C$10,"Software")

This formula adds Sales values only for rows where Region is West and Product is Software. If you need Excel to return a label for one row instead of adding records, use IF with AND and OR in Excel instead.

What SUMIFS adds

Think about the question before writing the formula:

If you need to count matching records instead of adding a numeric total, use How to Use COUNTIFS in Excel with Multiple Criteria.

In SUMIFS, each criterion is a pair: a range to check and the value or rule to look for in that range. A row contributes to the total only when it passes every pair.

Build a two-criteria SUMIFS formula

Use this small sales table. The values to add are in column D, while columns B and C decide which rows count.

=SUMIFS($D$5:$D$10,$B$5:$B$10,"West",$C$5:$C$10,"Software")

1. $D$5:$D$10 is the sum range: the Sales values Excel can add.
2. $B$5:$B$10,"West" is the first pair: keep rows where Region is West.
3. $C$5:$C$10,"Software" is the second pair: keep rows where Product is Software.

The matching rows are $1,200, $1,500, and $600, so the result is $3,300. The dollar signs keep each range fixed if you copy the formula; see Excel Cell References: Relative, Absolute, and Mixed if that part is new to you.

Use cells for a region and date range

Hard-coded text is useful for learning, but report controls are easier to reuse. Put the selected Region in F2, the Start date in F3, and the End date in F4. Then use:

=SUMIFS($D$5:$D$10,$B$5:$B$10,$F$2,$A$5:$A$10,">="&$F$3,$A$5:$A$10,"<"&$F$4+1)

For West from September 1 through September 30, the result is $4,000. The formula includes the September 30 sale at 3:30 PM because the final rule means “before October 1,” rather than “at or before midnight on September 30.”

The quotation marks hold the comparison operator as text, and & joins it to the date in the control cell. Make sure the Start and End cells contain real Excel dates, not text that only looks like a date.

Why each criterion must match

SUMIFS checks the criteria pairs on the same row. In the date-range formula, a record must be in West, on or after the Start date, and before the day after the End date. Only then does Excel add that row’s Sales value.

This is why the East Software sale does not count, even though it is in September, and why the October 1 West sale does not count, even though its Region matches.

When SUMIFS returns 0 or an error

Check whether any row actually matches every condition

A result of 0 can simply mean there are no matching rows. Check each condition separately: spelling, spaces, selected Region, dates, and comparison operators. For text typed directly into a formula, use quotation marks; for a value stored in a cell, use the cell reference without quotation marks.

Make the ranges the same size

The sum range and every criteria range need the same number of rows and columns. For example, do not use $D$5:$D$10 with $B$5:$B$9. Correct the ranges first if the formula reports an error. For help identifying the error itself, read Excel Formula Errors Explained.

Keep same-column alternatives separate

This tutorial uses requirements from different fields, such as Region and Product. If you need to include either of several values from one column, such as Software or Services, that is a separate OR-style SUMIFS problem. Do not repeat the same criteria range and expect it to mean “either value.”

Source and further reading

Microsoft documents the current SUMIFS syntax, criteria pairs, supported versions, and range-size requirement. For a worked Microsoft example of summing values based on more than one condition, see Sum values based on multiple conditions.