SUMIF explained, with practice exercises

In short: =SUMIF(range,criteria,sum_range). Excel tests each cell in range against the criteria and, for every match, adds the value from the same row of sum_range.

Try it now

The sheet lists orders with city, number of orders and revenue. Work out the revenue from Berlin. Click the yellow cell and write the formula.

Task In C8, calculate the revenue of all orders from Berlin.

	A	B	C
1	City	Orders	Revenue $
2	Berlin	3	1200
3	Hamburg	5	1800
4	Berlin	2	900
5	Munich	4	1500
6	Berlin	1	450
7
8	Berlin	Total revenue:

The SUMIF formula

Argument	Meaning	In the example
range	The cells tested against the criteria	A2:A6 (the cities)
criteria	The condition: text, a number, a comparison such as ">1000", or a cell reference	"Berlin"
sum_range	The cells whose values are added. Optional: leave it out and Excel adds range itself.	C2:C6 (the revenue)

The full formula is =SUMIF(A2:A6,"Berlin",C2:C6). Berlin appears in rows 2, 4 and 6, so Excel adds 1,200 + 900 + 450 and returns 2,550.

Criteria from a cell

Hard-coding "Berlin" works, but pointing to a cell is more flexible: change the cell and the total updates. That is how simple report sheets are built, with a category typed at the top and the result next to it. In the next exercise the category sits in E2.

Task In F2, use SUMIF to add up the spending for the category in E2. Use E2 as the criterion instead of typing the text.

	A	B	C	D	E	F
1	Category	Item	Amount $		Category	Total
2	Hardware	Laptop	899		Hardware
3	Office supplies	Printer paper	45
4	Furniture	Office chair	279
5	Hardware	Monitor	189
6	Office supplies	Toner	112
7	Hardware	Headset	59
8	Furniture	Desk	420

The answer is =SUMIF(A2:A8,E2,C2:C8). Note that E2 has no quotes. "E2" would look for the literal text E2 and return 0.

Numeric criteria

SUMIF filters on numbers too. Comparisons go in quotes, just as with COUNTIF:

• =SUMIF(B2:B6,">=3",C2:C6) adds the revenue of rows with 3 or more orders.
• =SUMIF(B2:B6,">"&E2,C2:C6) takes the threshold from E2. The operator stays in quotes and the reference is joined with an ampersand.
• =SUMIF(A2:A6,"M*",C2:C6) adds every city starting with M. Wildcards like * and ? only apply to text.

Leaving out sum_range

When the condition and the values live in the same column, skip the third argument. =SUMIF(C2:C6,">1000") adds every revenue above 1,000, which in the example is 1,200 + 1,800 + 1,500 = 4,500. Handy for questions like "how much comes from large orders?".

Several conditions: SUMIFS

SUMIF handles one condition. For two or more, say Berlin and at least 3 orders, use SUMIFS. Watch out: SUMIFS puts sum_range first.

Function	Formula
SUMIF	=SUMIF(A2:A6,"Berlin",C2:C6)
SUMIFS	=SUMIFS(C2:C6,A2:A6,"Berlin",B2:B6,">=3")

You can practise it on the SUMIFS page.

Common SUMIF mistakes

Arguments in the wrong order

In SUMIF the tested range comes first and sum_range last. If you are used to SUMIFS, it is easy to write =SUMIF(C2:C6,"Berlin",A2:A6). That returns 0, because none of the revenue cells says Berlin.

Criteria without quotes

=SUMIF(A2:A6,Berlin,C2:C6) returns #NAME?, and an unquoted comparison like >=3 is rejected before the formula is even entered. Only numbers and cell references go without quotes.

Ranges of different sizes

SUMIF does not complain here. If sum_range is shorter or longer than range, Excel quietly resizes it to match, starting from its first cell. The total can then come from the wrong rows. Always select both ranges with the same size.

Spaces and numbers stored as text

"Berlin " with a trailing space does not match "Berlin", and revenue stored as text is skipped. Both are typical after an import. Clean the text with TRIM and convert the values to numbers.

SUMIF in German Excel

In a German Excel the function is called SUMMEWENN and arguments are separated by semicolons: =SUMMEWENN(A2:A6;"Berlin";C2:C6). SUMIFS becomes SUMMEWENNS. Our exercises accept both spellings.

Which Excel versions have SUMIF?

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

FAQ