SUMIF With Multiple Criteria in Google Sheets
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.
| A | B | |
|---|---|---|
| 1 | Region | Amount |
| 2 | East | 120 |
| 3 | West | 90 |
| 4 | North | 200 |
| 5 | East | 60 |
| 6 | West | 150 |
| 7 | North | 80 |
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 want | Use |
|---|---|
| One condition | SUMIF |
| All conditions true (AND) | SUMIFS |
| Any condition true (OR) | Several SUMIF formulas added together |
| A range between two numbers | SUMIFS with ">=" and "<=" |
Common mistakes
- Adding a second criterion to SUMIF. It only accepts one.
- Using SUMIFS for OR. It will return 0 when no single row can match both values.
- The wrong argument order. In SUMIFS the sum range is first. In SUMIF it is last.
- Double counting. When OR conditions overlap, a row can be added twice.
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.
Exercise 2: Between two values
Write a formula that totals the amounts in B2:B7 that are at least 80 and at most 150.
What to learn next
- SUMIFS: add up values that meet several conditions.
- SUMIF: add up the rows that match a condition.
- COUNTIFS: count rows that meet several conditions.
Syntax and rules in this guide were checked against Google's SUMIF with multiple criteria help page. See all guides.