How to Use SUMIF in Google Sheets
=SUMIF(range, criterion, [sum_range]).
What SUMIF does
SUM adds every number in a range. SUMIF adds only the numbers that meet a test you set. You use it when a table has a label column (region, product, status) and a number column (sales, hours, cost), and you want a total for one label.
SUMIF syntax
=SUMIF(range, criterion, [sum_range])
range: the cells that are tested against the criterion.criterion: the test to apply, such as"East"or">100".sum_range(optional): the cells to add up, when they are different fromrange. If you leave it out, SUMIF adds the cells inrangeitself.
SUMIF supports one criterion. To test several conditions at once, use SUMIFS.
A step-by-step example
Here is a small sales table in cells A1:C7.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Sales |
| 2 | East | Notebook | 120 |
| 3 | West | Pen | 45 |
| 4 | East | Pen | 60 |
| 5 | North | Notebook | 80 |
| 6 | West | Notebook | 150 |
| 7 | East | Backpack | 200 |
To total the sales for the West region:
=SUMIF(A2:A7, "West", C2:C7)
This returns 195. Here is why:
- Sheets checks each cell in
A2:A7and finds the ones equal to"West". These are rows 3 and 6. - For those rows, it takes the matching cells from
C2:C7, which are45and150. - It adds them: 45 + 150 = 195.
Text criteria go in quotation marks. Keep the range and the sum range the same size, so each row lines up.
Use comparison operators
For numbers, you can put an operator in front of the value inside the quotation marks. To add only the sales that are greater than 100:
=SUMIF(C2:C7, ">100")
This returns 470 (120 + 150 + 200). There is no third argument, so SUMIF adds the cells it tested.
Google's help page lists these operators: = (equal), > (greater than) and < (less than). You can also combine them, as in ">=" and "<=".
To compare with a value in a cell, join the operator and the cell reference with an ampersand. If E1 holds 100, then =SUMIF(C2:C7, ">"&E1) gives the same result.
Use wildcards for partial matches
In a text criterion, ? matches any single character and * matches any run of characters (including none). To total every sale whose product starts with "Note":
=SUMIF(B2:B7, "Note*", C2:C7)
This returns 350 (120 + 80 + 150). To match a literal question mark or asterisk, put a tilde in front of it, as in "~?".
Common SUMIF mistakes
- Forgetting quotation marks. A text criterion must be in quotes:
"West", notWest. - Ranges of different sizes. If
A2:A7is paired withC2:C6, the rows no longer line up and the result is wrong. Make them the same size. - Numbers stored as text. Cells that contain text instead of numbers are not added. If a total looks too small, check that the numbers are real numbers.
- Needing two conditions. SUMIF takes one criterion. Use SUMIFS when you need, for example, East and Notebook.
- Hidden spaces. A cell holding
"East "(with a trailing space) does not equal"East". Clean the data withTRIM.
Try it yourself
Use the sales table above (A1:C7). Type your formula into the box, starting with =, then press Check.
Exercise 1: Total for one region
Write a formula that returns the total Sales (column C) for the East region (column A).
Exercise 2: Add only large sales
Write a formula that adds up only the Sales values (column C) that are greater than 100.
What to learn next
- COUNTIF: counts the rows that meet a condition instead of adding them.
- SUMIFS: adds with several conditions at once.
- VLOOKUP and XLOOKUP: fetch one value from a table instead of adding many.
Syntax and criterion rules in this guide were checked against Google's SUMIF help page. See all guides.