SUMIF in Excel: how to use it
SUMIF adds up only the rows you want: first where to look, then what to look for, then what to add up.
Try the first exercises for freeIn the lesson Adding up with SUMIF
What does SUMIF do?
SUMIF only adds up the numbers from the rows that meet a condition. You give it three things: where Excel checks the condition, what the condition is, and which column it should add up.
That way you get a total per group without a filter or a PivotTable: the revenue of one region, the spending of one month, the hours of one employee. If something changes in the list, the formula recalculates straight away.
When do you use SUMIF?
- The revenue of one region: =SUMIF(A2:A100,"Leeds",C2:C100).
- All amounts over 500: =SUMIF(C2:C100,">500").
- The hours of one employee in a timesheet: =SUMIF(A2:A60,"Sophie",D2:D60).
- A summary per region, with the region from a cell: =SUMIF($A$2:$A$100,F2,$C$2:$C$100).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =SUMIF(A2:A6,"Leeds",C2:C6)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
SUMIF | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | SUMIF |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
range | The column where you check the condition. | A2:A6 |
criteria | What you're looking for, for example "Leeds". | "Leeds" |
[sum_range] | Optional: the column with the amounts. | C2:C6 |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
SUMIF step by step
E1 contains =SUMIF(A2:A6,"Leeds",C2:C6). 1200 + 410: only the rows for Leeds. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- In E1, type the function with a bracket.
=SUMIF( - Select where Excel should look: the regions in A2 to A6. Type a comma.
=SUMIF(A2:A6, - Type what Excel should look for, in quotation marks: "Leeds". Type another comma.
=SUMIF(A2:A6,"Leeds", - Select what Excel should add up: the revenue in C2 to C6.
=SUMIF(A2:A6,"Leeds",C2:C6 - Close the bracket and press Enter: 1200 + 410 = 1610.
=SUMIF(A2:A6,"Leeds",C2:C6)
More examples
Adding up above a limit
E1 contains =SUMIF(C2:C6,">500"). Without the third part, Excel adds up the column itself: only the revenue figures over 500, so 1200 + 980.
With the region from a cell
F2 contains =SUMIF(A2:A6,E2,C2:C6). The region comes from E2. Type York there, and you get 415.
Matching part of a text
D1 contains =SUMIF(A2:A5,"Laptop *",B2:B5). "Laptop *", with a space before the asterisk, adds up the two laptops, but not the laptop/tablet stand: there's no space after Laptop there.
A summary per region
Put the regions in a column and refer to it instead of typing the name. Lock the ranges with $ and you can fill the formula down.
Adding up above or below an amount
A comparison goes in quotation marks. =SUMIF(C2:C6,">500") only adds up the amounts above 500. If the limit is in a cell, join it with &: =SUMIF(C2:C6,">"&E1).
Matching part of a text
An asterisk stands for any text. =SUMIF(B2:B6,"*laptop*",C2:C6) adds up every row that contains the word laptop, even with something before or after it.
More than one condition
Then you use SUMIFS. There, what you add up comes first: =SUMIFS(C2:C6,A2:A6,"Leeds",B2:B6,"Laptop") adds up the laptops in Leeds.
Where it often goes wrong
- In SUMIFS the sum range comes first, in SUMIF it comes last.
- A number stored as text isn't added up.
Practise this in the lesson Adding up with SUMIF. The first exercises are free.
Frequently asked questions
What's the difference with a PivotTable?
A formula updates straight away and fits into your own layout. A PivotTable is quicker to make, but you have to refresh it.