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

E1=SUMIFS(C2:C6,A2:A6,"North",B2:B6,"Shoes")
ABCDE
1RegionProductSales920
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

In plain words: Add up the sales in C2:C6 on the rows where the region in A2:A6 is North and the product in B2:B6 is Shoes.

E1=SUMIFS(C2:C6,A2:A6,"South",C2:C6,">200")
ABCDE
1RegionProductSales300
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

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.

Practice SUMIFS

Related functions