Excel SUMIFS with Multiple Criteria: Complete Guide (Includes Real-World Examples)

The SUMIFS function is the most powerful way to sum values based on multiple conditions in Excel and Google Sheets. Unlike SUMIF which handles only one condition, SUMIFS supports up to 127 criteria pairs.

SUMIFS syntax

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

Key rule: the sum range comes first (opposite of SUMIF!). Each criteria is a pair: a range and the condition for that range.

Example 1: Single criteria (like SUMIF)

Sum all sales in column C where region in column A is "East":

=SUMIFS(C:C, A:A, "East")

Example 2: Two criteria (AND logic)

Sum sales where region = "East" AND product = "Widget":

=SUMIFS(C:C, A:A, "East", B:B, "Widget")

Example 3: Date range criteria

Sum amounts between two dates on the same column:

=SUMIFS(C:C, A:A, ">=1/1/2026", A:A, "<=3/31/2026")

Better practice: use cell references with >= operator:

=SUMIFS(C:C, A:A, ">="&E1, A:A, "<="&F1)

Put start date in E1 and end date in F1 for easy updates.

Example 4: Numeric criteria with operators

Sum amounts greater than 500 where status is "Pending":

=SUMIFS(C:C, B:B, "Pending", C:C, ">500")

Example 5: Blank and non-blank criteria

Sum amounts where status column is blank:

=SUMIFS(C:C, B:B, "")

Sum amounts where status column is NOT blank:

=SUMIFS(C:C, B:B, "<>")

Common mistakes to avoid

• Wrong argument order: sum_range first! =SUMIFS(C:C, A:A, "X") not =SUMIFS(A:A, "X", C:C).
• Mismatched range sizes: all ranges must have the same number of rows. C1:C100 and A1:A100 work; C1:C100 and A1:A50 do not.
• Text not in quotes: "East" yes; East no. Use >0 for numbers, ">0" for text-style operators.
• Dates as text: use DATE() or cell references to avoid date format issues.

Generate your SUMIFS formula instantly with our SUMIFS formula generator.