How to Use SUMIFS in Google Sheets
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]).
What SUMIFS does
SUMIF handles one condition. SUMIFS handles one or more. You use it for questions such as "how much did we sell in the East region and of the product Pen?" A row is added only if it passes every test.
SUMIFS syntax
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
sum_range: the numbers to add up.criteria_range1: the range checked againstcriterion1.criterion1: the test, such as"East"or">100".criteria_range2, criterion2, ...(optional): more range and test pairs.
Note the order: the range to add comes first. In SUMIF it comes last. Google's help page also says that returning a sum across multiple columns is not supported, so sum_range should be a single column or row. Keep every range the same size.
A step-by-step example
Here is a small sales table in cells A1:C7.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Amount |
| 2 | East | Pen | 120 |
| 3 | West | Pen | 90 |
| 4 | East | Book | 200 |
| 5 | East | Pen | 60 |
| 6 | West | Book | 150 |
| 7 | East | Book | 80 |
To total the sales in the East region for the product Pen:
=SUMIFS(C2:C7, A2:A7, "East", B2:B7, "Pen")
This returns 180. Here is why:
- Sheets finds the rows where column A is East: rows 2, 4, 5 and 7.
- Of those, it keeps the rows where column B is Pen: rows 2 and 5.
- It adds the amounts in those rows: 120 + 60 = 180.
Mix text and number conditions
Each pair can use a different kind of test. To total East sales that are larger than 100:
=SUMIFS(C2:C7, A2:A7, "East", C2:C7, ">100")
This returns 320 (120 + 200). The same column can appear as both the sum range and a criteria range. To use a cell value in a test, join it with an ampersand, as in ">"&F1.
Common SUMIFS mistakes
- Using SUMIF order. In SUMIFS the sum range goes first.
- Mismatched range sizes.
C2:C7withA2:A6gives an error or wrong totals. - Wanting OR logic. SUMIFS requires all conditions to be true. For "East or West", add two SUMIFS together.
- Operators outside quotes. Write
">100", not>100.
Try it yourself
Use the sales table above (A1:C7). Type your formula into the box, starting with =, then press Check.
Exercise 1: Two conditions
Write a formula that totals the Amount (column C) for rows where the Region is East and the Product is Pen.
Exercise 2: Text plus a number
Write a formula that totals the Amount for East rows where the Amount is greater than 100.
What to learn next
- SUMIF: add up the rows that match a condition.
- COUNTIFS: count rows that meet several conditions.
- FILTER: pull out the rows that match.
- SUMIF with multiple criteria: AND, OR and between-two-values sums.
- AVERAGEIFS: averages values that meet several conditions.
Syntax and rules in this guide were checked against Google's SUMIFS help page. See all guides.