Math

SUMIFS

Definition

SUMIFS returns a total taken only from rows that meet every test you give it.

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

Arguments

Argument	Meaning
`sum_range`	The numbers to total when every test on that row passes, such as `C2:C5`.
`criteria_range1`	The first set of cells to check.
`criteria1`	The required value in that first set.
`criteria_range2`	The second set of cells to check.
`criteria2`	What the second set of cells must equal.

Worked example

A depot logs zone numbers, lane numbers, and carton counts in A2:C5.

A: Zone	B: Lane	C: Cartons
3	8	40
3	2	15
5	8	22
3	8	11

Formula: =SUMIFS(C2:C5,A2:A5,3,B2:B5,8)

Result: 51. Zone 3 and lane 8 appear together in rows 2 and 5, so 40 + 11 equals 51.

Practice

Cells A2:A5 hold 1, 1, 2, and 1. Cells B2:B5 hold 10, 20, 10, and 10. Cells C2:C5 hold 5, 8, 3, and 4. In D2, write a formula that totals C2:C5 where A2:A5 equals 1 and B2:B5 equals 10.

In D2, write a SUMIFS formula that adds C2:C5 where A2:A5 equals 1 and B2:B5 equals 10.
D2 should show the total of C2:C5 where A2:A5 equals 1 and B2:B5 equals 10.

Step 1 of 2

Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.

Loading the sheet…

Related functions

SUMIF totals cells that pass one test. COUNTIF counts those matches. SUMIFS adds a second test before it totals.

Common mistake

Placing the numbers to add last, as SUMIF does, for example =SUMIFS(A2:A5,3,B2:B5,8,C2:C5). SUMIFS needs those numbers in the first argument.