Excel SUMIFS Function

SUMIFS adds up values in a range that meet multiple conditions you specify. Unlike SUMIF, which only checks one condition, SUMIFS can evaluate up to 127 — region, date, product, all in a single formula. The one thing that trips up almost everyone switching over from SUMIF: the argument order flips. Sum range comes first in SUMIFS. In SUMIF, it comes last.

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Parameter	Required	Description
sum_range	Yes	The range of numbers you want added up.
criteria_range1	Yes	The first range to check against a condition.
criteria1	Yes	The condition criteria_range1 must meet. A number, text, cell reference, or expression like ">100".
criteria_range2, criteria2	No	Additional range/condition pairs. Add as many as you need — up to 127 pairs total.

Basic Example

You're tracking sales by region and product. To total sales for laptops sold in the West region:

=SUMIFS(sales_amount, region, "West", product, "Laptops")

// sales_amount  — the column of numbers to add up
// region        — the range to check for "West"
// product       — the range to check for "Laptops"

SUMIFS scans every row. Only rows where region equals "West" and product equals "Laptops" get included in the total. Both conditions have to be true — there's no partial credit.

How It Works

Every condition must be true

SUMIFS uses AND logic across all criteria pairs. A row only gets added to the sum if it satisfies every single condition. Need OR logic instead — say, sum sales for either "West" or "East"? SUMIFS can't do that on its own. Add two SUMIFS formulas together, or use SUMPRODUCT.

Ranges have to match in size

sum_range, criteria_range1, criteria_range2, and every other range argument must cover the same number of rows. If sum_range is B2:B500 and criteria_range1 is C2:C450, SUMIFS returns #VALUE!. Select full columns or matching row counts, always.

Text criteria go in quotes, references don't

"West" needs quotes. A cell reference like H2 doesn't. Mixing this up is the single most common typo in SUMIFS formulas — and it fails silently, returning 0 instead of an error.

Comparison operators need quotes and a concatenation

To check for "greater than" or "less than," wrap the operator in quotes and join it to a value or cell reference with &:

=SUMIFS(amount, order_date, ">="&DATE(2026,1,1))

Without the &, Excel treats ">="&DATE(2026,1,1) as two separate arguments and the formula breaks.

SUMIFS vs. SUMIF

SUMIF handles one condition. SUMIFS handles many — that's the entire difference, but it changes how you write the formula.

// SUMIF: sum_range goes last
=SUMIF(region, "West", sales_amount)

// SUMIFS: sum_range goes first
=SUMIFS(sales_amount, region, "West")

If you're used to SUMIF and switch to SUMIFS without noticing the order flip, you'll sum the wrong column. Excel won't warn you — the formula still calculates, just against the wrong data. When in doubt, use SUMIFS even for a single condition. It behaves consistently and scales without a rewrite the moment you need a second criterion.

Common Use Cases

Sum sales within a date range

Building a monthly commission report? Sum orders placed between two dates using >= and <= as separate criteria pairs:

=SUMIFS(order_total, order_date, ">="&DATE(2026,3,1), order_date, "<="&DATE(2026,3,31))

order_date appears twice — once for each boundary of the range.

Sum by two categories at once

A retail sales tracker with 40,000 rows across a dozen regions and 200 SKUs. Total revenue for a specific product in a specific region:

=SUMIFS(revenue, product_sku, "SKU-4471", store_region, "Midwest")

Add a third criteria pair — say, a sales rep column — and the same formula narrows further without restructuring anything.

Match partial text with wildcards

Product names aren't always consistent. To sum every row where the product name contains "Laptop," regardless of what comes before or after:

=SUMIFS(sales_amount, product, "*Laptop*")

The asterisk matches any characters. Use ? instead to match exactly one character.

Exclude a specific value

Sum all sales except returns, where returns are flagged with the text "Return" in a status column:

=SUMIFS(sales_amount, status, "<>Return")

<> means "not equal to." Combine it with other criteria pairs the same way you'd combine any condition.

Handling Common Errors

SUMIFS most often fails with #VALUE!, and it's almost always a range size mismatch.

Common causes of #VALUE!:

• A criteria range has more or fewer rows than sum_range
• One argument references an entire column (B:B) while another is a fixed range (B2:B500)
• A criteria range accidentally includes a text header row that sum_range doesn't

SUMIFS can also silently return 0 with no error at all. That's not a thrown error, so IFERROR won't catch it or tell you anything useful — it just means no rows matched every condition. When that happens, check it manually: look for typos in your criteria text, extra spaces in the data (wrap ranges in TRIM if needed), or a date stored as text instead of an actual date. A 0 result is a debugging problem, not an error-handling one.

Known Limitations

Limitation	Workaround
AND logic only — can't check "this OR that"	Add multiple SUMIFS formulas together, or use SUMPRODUCT
All ranges must be equal size	Select matching ranges for every argument
Not case-sensitive ("West" = "west")	Use SUMPRODUCT with EXACT
Can't sum based on cell color or formatting	Add a helper column that flags the formatting, then sum on that

Related Functions

Function	Use this when...
SUMIF	You only need to check one condition.
COUNTIFS	You want a count of matching rows, not a total.
VLOOKUP	You need to pull a single value from a table rather than sum a set of matching rows.