SUMIFS explained, with practice exercises

In short: =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…). The numbers to add come first, followed by pairs of range and criteria. A row is added only when every criteria matches.

Try it now

The sheet shows revenue by region and product. Work out the coffee revenue in the North region. Click the yellow cell and write the formula.

Task In E2, use SUMIFS to calculate the coffee revenue in the North region.

	A	B	C	D	E
1	Region	Product	Revenue $		Revenue North, Coffee
2	North	Coffee	1250
3	South	Coffee	980
4	North	Tea	640
5	West	Coffee	1100
6	North	Coffee	870
7	South	Tea	520
8	North	Coffee	410

The SUMIFS formula

Argument	Meaning	In the example
sum_range	The cells whose values are added	C2:C8 (the revenue)
criteria_range1	The first range to test	A2:A8 (the regions)
criteria1	The condition for the first range	"North"
criteria_range2, criteria2	More range and condition pairs, up to 127 in total	B2:B8 and "Coffee"

The full formula is =SUMIFS(C2:C8,A2:A8,"North",B2:B8,"Coffee"). Only rows where North and Coffee appear together are added. The conditions are joined with AND: if one fails, the row is skipped.

How SUMIFS differs from SUMIF

In SUMIF the sum range is the last argument. In SUMIFS it is the first. Since SUMIFS can take any number of criteria pairs, the sum range needs a fixed slot at the front.

Function	Formula
SUMIF	=SUMIF(A2:A8,"North",C2:C8)
SUMIFS	=SUMIFS(C2:C8,A2:A8,"North")

Both return the same total. Plenty of people use SUMIFS even for a single condition so they only have one argument order to remember.

One more: a number range with two limits

A classic reporting task: one department's costs for a period. The month is stored as a number in column A. For "from ... to", test the same column twice, once with each limit.

Task In E2, calculate the Marketing department's costs for months 4 to 6 (April to June).

	A	B	C	D	E
1	Month	Department	Costs $		Marketing, months 4 to 6
2	3	Marketing	2400
3	4	Marketing	1800
4	4	Sales	3100
5	5	Marketing	950
6	6	HR	1200
7	6	Marketing	2100
8	7	Marketing	1600

The answer is =SUMIFS(C2:C8,B2:B8,"Marketing",A2:A8,">=4",A2:A8,"<=6"). Repeating A2:A8 is not a mistake. It is exactly how you express a range of values.

Criteria from cells

In a real report the criteria usually sit in their own cells, so you can change them without touching the formula. With the region in F1 and the start month in F2:

=SUMIFS(C2:C8,A2:A8,F1,B2:B8,">="&F2)

A plain reference like F1 needs no quotes. For a comparison, keep the operator in quotes and join the reference with an ampersand. Lock the ranges with dollar signs, such as $C$2:$C$8, and you can copy one formula across a whole summary grid with regions down the side and products across the top.

Common SUMIFS mistakes

Using the SUMIF argument order

=SUMIFS(A2:A8,"North",C2:C8) is the classic slip. SUMIFS expects a range in second place, not text, so the formula does not work. The sum range goes first.

Ranges of different sizes

Every range must have the same number of rows and columns. =SUMIFS(C2:C8,A2:A9,"North") returns #VALUE!. Unlike SUMIF, SUMIFS does not resize anything quietly, which at least makes the problem visible.

Expecting OR instead of AND

SUMIFS joins all conditions with AND. =SUMIFS(C2:C8,A2:A8,"North",A2:A8,"South") returns 0, because no row is North and South at once. For OR, add two formulas: =SUMIFS(C2:C8,A2:A8,"North")+SUMIFS(C2:C8,A2:A8,"South").

Comparisons without quotes

Criteria like >=4 must be text in quotes, otherwise Excel rejects the formula. Unquoted text leads to #NAME?.

SUMIFS in German Excel

In a German Excel the function is called SUMMEWENNS and arguments are separated by semicolons: =SUMMEWENNS(C2:C8;A2:A8;"Nord";B2:B8;"Kaffee"). COUNTIFS becomes ZÄHLENWENNS. Our exercises accept both spellings.

Which Excel versions have SUMIFS?

All current ones, including Excel 2016 and 2019, Excel for Mac and Excel for the web.

FAQ