How to Use SUMIFS for Multiple Conditions in Excel

SUMIF totals values that meet one condition. The moment you need two or more — sales in the East region and in June — you need SUMIFS. It's one of the most useful business formulas there is, and once you see the pattern it's easy.

The syntax (note the order change).

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

The big gotcha: in SUMIFS the range you're adding comes first. In SUMIF it comes last. Mixing them up is the number-one SUMIFS error.

A worked example. Sales amount in column D, region in B, month in C. Total East-region June sales:

=SUMIFS(D2:D100, B2:B100, "East", C2:C100, "June")

Read it as: sum column D, where column B is "East" AND column C is "June." Every condition must be true for a row to count.

Reference criteria from cells. Make it dynamic by pointing to selector cells:

=SUMIFS(D2:D100, B2:B100, G1, C2:C100, G2)

Now change G1 and G2 and the total updates — the basis of a simple dashboard.

Date ranges. A common need is totaling between two dates. Use two conditions on the same date column:

=SUMIFS(D2:D100, A2:A100, ">="&G1, A2:A100, "<="&G2)

where G1 and G2 hold the start and end dates. Note the operator is in quotes and joined to the cell with &.

Number and comparison criteria. Same pattern: ">1000", "<="&H1, "<>0" (not zero). Text criteria support wildcards too — "North*" matches anything starting with "North."

Common mistakes.

Wrong argument order — sum_range first; everyone forgets this coming from SUMIF.
Ranges of different sizes — every criteria_range and the sum_range must span the same rows, or you'll get a #VALUE!.
Operators not quoted — ">="&G1, not >=G1.
AND vs OR confusion — SUMIFS is always AND (all conditions must match). For OR logic, add two SUMIFS together or rethink the criteria.

SUMIFS vs a PivotTable. For ad-hoc exploration, a PivotTable is faster. For a fixed report that updates live with your data — a dashboard cell, a monthly summary — SUMIFS wins because it sits right in your layout and recalculates automatically.