SUMIF With Multiple Criteria in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Intermediate · 7 min read · includes 2 free exercises

In short: SUMIF supports only one criterion. For AND (all conditions true), use SUMIFS. For OR (either condition), add two SUMIF formulas together. To sum values between two numbers, use SUMIFS with two conditions on the same column.

SUMIF takes only one criterion

Google's help page for SUMIF states that it supports only one criterion, and recommends the database function DSUM when you need several. In practice most people reach for SUMIFS instead, and combine formulas when the logic is OR. The right tool depends on how the conditions relate to each other.

The example

Here are sales by region in cells A1:B7.

AB
1RegionAmount
2East120
3West90
4North200
5East60
6West150
7North80

AND: all conditions must be true

For "East and more than 100", use SUMIFS. The sum range comes first, followed by a range and criterion for each condition:

=SUMIFS(B2:B7, A2:A7, "East", B2:B7, ">100")

This returns 120, the only East row above 100. The SUMIFS guide covers this in detail.

OR: either condition can be true

SUMIFS cannot express "East or West", because it needs every condition to hold on the same row. Instead, run one SUMIF for each value and add the results:

=SUMIF(A2:A7, "East", B2:B7) + SUMIF(A2:A7, "West", B2:B7)

This returns 420: East is 180 and West is 240. For a longer list you can pass the values as an array and total them in one formula: =SUM(SUMIF(A2:A7, {"East","West"}, B2:B7)). For two or three values, added SUMIFs are the clearest.

Between two values

To sum amounts from 80 to 150, apply two conditions to the same column:

=SUMIFS(B2:B7, B2:B7, ">=80", B2:B7, "<=150")

This returns 440 (120 + 90 + 150 + 80). The operators go inside the quotation marks. To compare with a value in a cell, join them with an ampersand, as in ">="&D1.

Choosing the right formula

You wantUse
One conditionSUMIF
All conditions true (AND)SUMIFS
Any condition true (OR)Several SUMIF formulas added together
A range between two numbersSUMIFS with ">=" and "<="

Common mistakes

Try it yourself

Use the sales table above (A1:B7). Type your formula into the box, starting with =, then press Check.

Exercise 1: OR: East or West

Write a formula that totals the Amount (column B) for rows where the Region is East or West.

Hint: add two SUMIF formulas.

Exercise 2: Between two values

Write a formula that totals the amounts in B2:B7 that are at least 80 and at most 150.

Hint: SUMIFS with two conditions on column B.

What to learn next

Syntax and rules in this guide were checked against Google's SUMIF with multiple criteria help page. See all guides.