sheets.org
Formulas & Functions

Master SUMIFS, COUNTIFS & AVERAGEIFS: Conditional Math Done Right

Once your data has more than one dimension, plain SUM and COUNT stop being enough. The questions become conditional: total revenue for the West region, count of orders in March, average deal size over \$500. The IFS family — SUMIFS, COUNTIFS, AVERAGEIFS — answers all of these with one formula each, identically in Google Sheets and Excel.

The shape of every IFS formula

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
=SUMIFS(D:D, B:B, "West", C:C, "Gadget")

Read it as: sum column D, where column B says West and column C says Gadget. Add as many range/criterion pairs as you need — every pair must be true for a row to count. COUNTIFS drops the first argument (nothing to sum), and AVERAGEIFS works exactly like SUMIFS.

Criteria syntax cheat sheet

  • Exact text: "West" — case-insensitive.
  • Comparison: ">500", "<=100", "<>"&"" (not blank). Comparisons are always quoted strings.
  • Cell reference: ">"&F1 — concatenate the operator with the cell. This trips everyone up once.
  • Wildcards: "West*" (starts with), "*corp*" (contains), ? for a single character.

The date-range pattern (memorize this one)

"How much did we sell in March 2026?" is two conditions on the same column — on or after March 1, before April 1:

=SUMIFS(D:D, A:A, ">="&DATE(2026,3,1), A:A, "<"&DATE(2026,4,1))

Using < with the first of the next month cleanly sidesteps 28-vs-31-day months and any time-of-day component hiding in your timestamps.

A dashboard in four formulas

Point the criteria at input cells (say, region in G1 and month start in G2) and you have an interactive summary:

Orders:   =COUNTIFS(B:B, G1, A:A, ">="&G2, A:A, "<"&EDATE(G2,1))
Revenue:  =SUMIFS(D:D, B:B, G1, A:A, ">="&G2, A:A, "<"&EDATE(G2,1))
Avg deal: =AVERAGEIFS(D:D, B:B, G1, A:A, ">="&G2, A:A, "<"&EDATE(G2,1))

Change G1 to "East" and everything recalculates. This is the formula-based sibling of a pivot table — better when you want a fixed layout that other formulas can build on.

Gotchas

  • Use the S versions. Old single-condition SUMIF/COUNTIF put arguments in a different order; SUMIFS handles one condition or many with consistent syntax. There is no reason to teach your fingers both.
  • All ranges must be the same size. A2:A100 with B2:B99 is an error (Excel) or silent misalignment risk (Sheets).
  • OR logic needs two formulas. The pairs are ANDed. West or East is =SUMIFS(...West...) + SUMIFS(...East...), or a SUMPRODUCT for fancier cases.
  • Numbers stored as text quietly match nothing. If a result looks too low, check for the little green triangles or use VALUE() to clean the column.