SUMIFS and COUNTIFS in Excel: Complete Guide with Examples

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

SUMIFS vs SUMIF: What Changed?

The original SUMIF function only supports one condition. SUMIFS (introduced in Excel 2007) supports up to 127 criteria pairs. The argument order is also different — in SUMIFS, the sum_range comes first:

' Old SUMIF (one condition):
=SUMIF(A:A, "Widget", B:B)

' SUMIFS (one or more conditions — use this in all new work):
=SUMIFS(B:B, A:A, "Widget")
=SUMIFS(B:B, A:A, "Widget", C:C, "North")  ' two conditions

Recommendation: use SUMIFS everywhere, even when you only have one condition. The argument order is consistent with COUNTIFS and AVERAGEIFS, which makes formulas easier to read and extend.

For the official reference, see Microsoft's SUMIFS documentation and COUNTIFS documentation.

SUMIFS Examples

Sum by One Text Condition

' Sum column B where column A = "Widget"
=SUMIFS(B:B, A:A, "Widget")

Sum by Two Conditions

' Sum sales (column C) where product (A) = "Widget" AND region (B) = "North"
=SUMIFS(C:C, A:A, "Widget", B:B, "North")

Sum with Comparison Operators

' Sum sales where amount > 1000
=SUMIFS(B:B, B:B, ">1000")

' Sum sales where quantity is not zero
=SUMIFS(C:C, D:D, "<>0")

Sum with a Date Range

' Sum sales between two dates (dates in E1 and E2)
=SUMIFS(C:C, A:A, ">="&E1, A:A, "<="&E2)

' Sum sales in January 2026 (hardcoded):
=SUMIFS(C:C, A:A, ">="&DATE(2026,1,1), A:A, "<="&DATE(2026,1,31))

Always use DATE() or cell references for date criteria — never put dates directly in quotes like ">=01/01/2026", as date formatting varies between locales and Excel versions.

Sum with Wildcards

' Sum all products starting with "Widget"
=SUMIFS(B:B, A:A, "Widget*")

' Sum all products containing "Ltd" anywhere
=SUMIFS(B:B, A:A, "*Ltd*")

' Combine wildcard with other criteria
=SUMIFS(B:B, A:A, "Widget*", C:C, "North")

Sum Where Cells Are Blank or Not Blank

' Sum only rows where column D has no value (blank)
=SUMIFS(B:B, D:D, "")

' Sum only rows where column D has any value (not blank)
=SUMIFS(B:B, D:D, "<>")

COUNTIFS Examples

COUNTIFS counts rows matching all conditions. There is no separate sum_range — just criteria pairs:

' Count rows where product = "Widget"
=COUNTIFS(A:A, "Widget")

' Count rows where product = "Widget" AND region = "North"
=COUNTIFS(A:A, "Widget", B:B, "North")

' Count rows where sales > 1000 AND < 5000
=COUNTIFS(C:C, ">1000", C:C, "<5000")

' Count rows with a date in Q1 2026
=COUNTIFS(A:A, ">="&DATE(2026,1,1), A:A, "<="&DATE(2026,3,31))

Why Does SUMIFS Return 0 When It Should Not?

This is the most common SUMIFS problem. Four causes to check in order:

1. Numbers Stored as Text in the Criteria Range

If your criteria range contains numbers formatted as text (left-aligned, green triangle in corner), a numeric criterion will never match. Test with =ISNUMBER(A2) — if it returns FALSE, the cell contains a text number.

Fix: Convert using the warning icon → "Convert to Number", or use =VALUE(A2) in a helper column. See the full numbers stored as text guide.

2. Criteria Range and Sum Range Are Different Sizes

All ranges in SUMIFS must be the same dimensions. =SUMIFS(B2:B100, A:A, "Widget") uses the full column A but only rows 2–100 of B — the mismatch may produce 0 or incorrect results.

Fix: Use consistent ranges: =SUMIFS(B2:B100, A2:A100, "Widget").

3. Extra Spaces in Criteria Cells

"Widget" does not match "Widget " (trailing space). Use =TRIM() on your data to clean it, or use a wildcard: "*Widget*".

4. Date Format Mismatch

If dates in your range are stored as text (formatted as strings rather than Excel date serial numbers), date criteria will not match. Check with =ISNUMBER(A2) — real Excel dates are numbers. Text dates must be converted first.

SUMPRODUCT as an Alternative to SUMIFS

For complex multi-condition calculations that SUMIFS can't handle easily (OR conditions, for example), use SUMPRODUCT:

' Sum where product is "Widget" OR "Gadget" (OR condition — SUMIFS can't do this cleanly)
=SUMPRODUCT(((A2:A100="Widget")+(A2:A100="Gadget"))*(B2:B100))

' More readable version using SUMIFS twice:
=SUMIFS(B:B, A:A, "Widget") + SUMIFS(B:B, A:A, "Gadget")

Dynamic SUMIFS with Cell References as Criteria

Use cell references for interactive dashboards where the criteria changes:

' Cell E1 contains the product name, E2 contains the region
=SUMIFS(C:C, A:A, E1, B:B, E2)

' Partial match using cell reference + wildcard:
=SUMIFS(C:C, A:A, "*"&E1&"*")