SUMIFS and COUNTIFS: 10 Practical Examples
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=COUNTIFS(criteria_range1, criteria1, ...)Assume A = Date, B = Region, C = Product, D = Sales.
- Sales in the North:
=SUMIFS(D:D, B:B, "North") - North sales of Widgets:
=SUMIFS(D:D, B:B, "North", C:C, "Widget") - Sales over 500:
=SUMIFS(D:D, D:D, ">500") - Not the North:
=SUMIFS(D:D, B:B, "<>North") - Products containing "pro":
=SUMIFS(D:D, C:C, "*pro*") - Between two dates in F1 and G1:
=SUMIFS(D:D, A:A, ">="&F1, A:A, "<="&G1) - Criterion from a cell:
=SUMIFS(D:D, B:B, H2) - Count of North orders:
=COUNTIFS(B:B, "North") - Count of non-blank cells:
=COUNTIFS(C:C, "<>") - Count of blank cells:
=COUNTIFS(C:C, "")
Common mistakes
All ranges must be the same size, and operators with cell references need the & join, as in the date example.
Put it to work
Our Christmas budget tracker uses SUMIFS to total holiday spending per person.
Want totals for every category at once without writing a formula for each? Try a pivot table.