SUMIFS in Google Sheets
Adds the cells that meet every one of several conditions. The sum range comes first.
In Google Sheets
SUMIFS takes the same arguments in Google Sheets: the sum range first, then pairs of range and criterion.
How to use SUMIFS
SUMIFS adds amounts when several conditions are true at the same time. Think of a sales list where you only want the North region and only shoes: a row counts when it passes every test.
The big difference from SUMIF is the argument order. SUMIFS starts with the range to add, then takes pairs of a range to check and its condition: =SUMIFS(C2:C10,A2:A10,"North",B2:B10,"Shoes"). Because the sum range comes first, you can add as many pairs as you need, but every range must have the same number of rows.
The conditions are joined with AND. For a period, use the same date column twice, once with ">="&F1 and once with "<="&F2. Operators, wildcards such as "*shoe*", and cell references work as in SUMIF.
With a single condition, SUMIF is a little shorter. To count the matching rows instead of adding them, use COUNTIFS, which has no sum range at all. When either of two conditions may be true, add two SUMIFS together.
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)- sum_range
- The cells to add.
- criteria_range1
- The cells the first condition is checked against.
- criteria1
- The first condition.
- criteria_range2, criteria2 (optional)
- More range and condition pairs, up to 127.
Examples
=SUMIFS(1C2:C6,2A2:A6,3"North",4B2:B6,5"Shoes")| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 920 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
In plain words: Add up 1the sales in C2:C6 on the rows where 2the region in A2:A6 is 3North and 4the product in B2:B6 is 5Shoes.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 300 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
Common SUMIFS mistakes
SUMIFS returns #VALUE!
The ranges are not the same size, as in
=SUMIFS(C2:C6,A2:A7,"North"). Make every range cover the same rows.Changing SUMIF to SUMIFS gives 0, an error or the wrong total
The sum range moved. SUMIF ends with it and SUMIFS starts with it, so
=SUMIF(A2:A6,"North",C2:C6)becomes=SUMIFS(C2:C6,A2:A6,"North").SUMIFS returns 0 when I ask for two values in the same column
The conditions are joined with AND, and no cell is both North and South, so
=SUMIFS(C2:C6,A2:A6,"North",A2:A6,"South")gives 0. Add two SUMIFS instead:=SUMIFS(C2:C6,A2:A6,"North")+SUMIFS(C2:C6,A2:A6,"South").A date condition adds nothing
A date typed inside quotes depends on the date settings of the file, and dates stored as text never match. Build the date with DATE:
=SUMIFS(C2:C100,A2:A100,">="&DATE(2024,1,1),A2:A100,"<"&DATE(2024,2,1))adds up January. Also check that the dates are real dates and not text.