Summing Across Conditions With SUMIFS

You already know SUMIF totals values that match a single rule, like all sales from the East region. But real questions are usually layered: What were East region sales in January? That's two conditions at once.

This is where SUMIFS shines. The trailing S means it can stack many criteria together, and a row is only added to the total when it passes every test you give it.

In this lesson you'll learn the argument order, write your first multi-criteria sum, and avoid the classic mistakes that trip people up.

SUMIFS flips the order you might expect from SUMIF. The numbers you add come first, then each condition is given as a pair.

• sum_range — the values to total
• criteria_range1, criteria1 — the first test
• criteria_range2, criteria2 — the second test

You can keep adding range and criteria pairs up to 127 conditions. Read the pattern below out loud: sum this, where this equals that, and where this equals that.

Imagine a table where column A holds the Region, column B the Month, and column C the Amount. You want total sales for the East region in January.

The amounts you add live in C:C. The first condition checks A:A for "East", and the second checks B:B for "January". A row only counts when both are true.

This is the single most common SUMIFS error. The sum_range and every criteria_range must have identical dimensions — the same number of rows and columns.

If your sum_range is C2:C100 but a criteria range is A2:A99, the formula returns a #VALUE! error because the rows don't line up.

The safest habit is to use the same start and end rows for all ranges, or use whole columns like A:A consistently.

Hard-coding "East" in quotes works, but a flexible report lets the user pick. Put the region in cell F1 and the month in F2, then reference those cells as your criteria.

Now changing F1 or F2 instantly recalculates the total. Notice that a plain cell reference needs no quotation marks — the quotes are only for literal text typed inside the formula.

Criteria aren't limited to exact text. You can use comparison operators for numbers by wrapping them in quotes.

• ">100" — greater than 100
• "<=50" — less than or equal to 50
• "<>0" — not equal to zero

Here we total amounts in the East region but only rows where the amount itself is greater than 100. Note the sum_range and a criteria_range can be the same column.

What if the threshold lives in a cell instead of being typed? You can't just write ">F1" — that searches for the literal text F1. Instead, you join the operator to the cell with the & symbol.

So ">"&F1 builds the criteria greater than whatever F1 contains. This concatenation trick is essential for dynamic, user-driven reports.

SUMIFS scales easily. Add another range and criteria pair for each new rule. Suppose column D holds the Salesperson. You can total East region sales, in January, made by "Maria".

Each condition narrows the result further. Because SUMIFS uses AND logic, a row must satisfy all three tests to be included in the sum.

Text criteria support wildcards. The asterisk * matches any number of characters and the question mark ? matches exactly one.

• "North*" matches North, Northeast, Northwest
• "*east*" matches anything containing east

This totals amounts for any region that starts with "North", which is handy when your region names share a prefix.

It's worth memorizing the difference because the argument order is reversed:

• SUMIF: range, criteria, [sum_range] — the range to test comes first, sum_range is optional and last.
• SUMIFS: sum_range, criteria_range1, criteria1, ... — the sum_range always comes first.

Tip: if you ever have more than one condition, reach straight for SUMIFS. Many people use SUMIFS even for a single criterion just to keep one consistent pattern.

When SUMIFS returns 0, it usually means no row matched all conditions — not that the formula is broken. Double-check for hidden spaces in your text, mismatched spelling, or a number stored as text.

A quick diagnostic is to remove one condition at a time. If the total appears once you drop a criterion, that criterion was the one excluding everything. This isolate-and-test habit makes debugging multi-criteria formulas fast.

Test your understanding of the SUMIFS argument order.

You can now total numbers across several conditions at once:

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — sum_range comes first.
• All ranges must be the same size, or you get #VALUE!.
• Use ">"&F1 to compare against a cell, and * / ? for wildcards.
• Conditions use AND logic — a row must pass them all.

Next up: counting rows across multiple conditions with COUNTIFS.