SUMIFS adds up the cells in a range that satisfy a condition. The single-criterion form is genuinely simple; the gotchas show up when you move to SUMIFS for multiple criteria, when the criteria reference dates, or when you want a SUM with OR logic. This tutorial covers all three.

SUMIFS basics

=SUMIFS(Status:Status, "Paid", Amount:Amount)

Three arguments: the range to test, the criterion, and the range to sum. SUMIFS pairs cells positionally — the third Amount cell is summed when the third Status cell matches. The two ranges must be the same size and shape; pass whole columns or use named ranges to keep them in sync.

SUMIFS for multiple AND conditions

=SUMIFS(Amount:Amount, Status:Status, "Paid", Channel:Channel, "Web", Date:Date, ">="&DATE(2025,1,1))

SUMIFS swaps the argument order: the sum range comes first, then range/criterion pairs. Every test is AND-ed. Like COUNTIFS, every range must be the same size, dates need DATE() to avoid locale issues, and operators sit inside the criterion string.

OR logic via addition

Sheets does not have a SUMIFS_OR. For two alternatives, add two SUMIFS together. For more, the cleanest pattern is =SUM(SUMIFS(Amount:Amount, Status:Status, {"Paid","Refunded","Trial"}, Channel:Channel, "Web")). The array criterion produces three SUMIFS results, and the outer SUM collapses them to a single number.

Sums by date range

The reliable pattern is the same one COUNTIFS uses: SUMIFS(Amount, Date, ">="&start, Date, "<"&next_start). Always anchor the upper bound with strict less-than against the start of the next period — that keeps month and year boundaries clean and sidesteps midnight-vs-23:59 off-by-one bugs that bite at the end of long months.

Worked examples

Total Paid revenue from Web in January

=SUMIFS(Amount:Amount, Status:Status, "Paid", Channel:Channel, "Web", Date:Date, ">="&DATE(2025,1,1), Date:Date, "<"&DATE(2025,2,1))

All conditions AND-ed. Date bounds cover Jan 1 inclusive through Jan 31 inclusive.

Revenue from any of three statuses

=SUM(SUMIFS(Amount:Amount, Status:Status, {"Paid","Refunded","Trial"}))

The array criterion yields one SUMIFS per status; SUM collapses them.

Sum where Notes contains "VIP"

=SUMIFS(Notes:Notes, "*VIP*", Amount:Amount)

Wildcards work in the criterion the same way COUNTIF treats them.

Common mistakes (and how to avoid them)

  • Mismatched range sizes between the criterion range and the sum range — return is #VALUE! or worse, silently wrong.
  • Hardcoding date strings ("2025-01-15") that work in your locale but fail for collaborators in another locale. Use DATE().
  • Forgetting that SUMIFS skips text in the sum range; SUMIFS treats text-as-numbers ("100") as zero. Coerce with VALUE() or fix at the source.
  • Putting the operator outside the quotes: SUMIFS(A:A, >100, B:B) is invalid; SUMIFS(A:A, ">100", B:B) is correct.

Tips that pay off later

  • When the same SUMIFS appears in many cells with different criteria, build a small helper table and wrap the lot in ARRAYFORMULA.
  • If you find yourself stacking SUMIFS for many groups, switch to QUERY("SELECT A, SUM(B) GROUP BY A") — one formula, all groups.
  • For weighted sums (price × qty by category), SUMPRODUCT beats SUMIFS in clarity.

When to reach for something else

When you need a full pivot — sums by row and column simultaneously — drop SUMIFS and use a built-in Pivot Table or QUERY ... PIVOT.

Functions used in this tutorial

SUMIF → SUMIFS → COUNTIF → COUNTIFS → QUERY →