Excel SUMIF and SUMIFS
SUMIF adds up the cells that meet one condition: =SUMIF(range, criteria, sum_range). SUMIFS does the same with several conditions, and takes the range to add first: =SUMIFS(sum_range, criteria_range1, criteria1, ...).
=SUMIF(range, criteria, [sum_range]) | SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
range- SUMIF: the cells to test against the condition, for example the Region column.
criteria- The condition: text such as
"East", a number, a cell such asF3, or a comparison such as">100". sum_range- SUMIF: the cells to add up, for example the Revenue column. It must have the same size as
range. If you leave it out, SUMIF adds uprangeitself. sum_range (SUMIFS)- SUMIFS: the cells to add up. In SUMIFS it comes first, not last.
criteria_range, criteria (SUMIFS)- Pairs of a range and its condition. Every pair must cover the same number of rows, and a row is added only when all conditions are true.
How SUMIF works
SUMIF looks down one column, finds the rows where a condition is true, and adds up the numbers from another column in those same rows. In the sheet below, the regions are in A2:A11 and the revenue is in D2:D11. To add up the revenue of the East region:
=SUMIF(A2:A11, "East", D2:D11)
- The first argument is the column to test:
A2:A11. - The second argument is the condition: the text
"East". - The third argument is the column to add up:
D2:D11. Excel adds the revenue of every row where column A says East.
The test range and the sum range are separate on purpose. You test one column and add another. If the two ranges are different sizes, the results go wrong, so keep them the same height.
Writing the criteria
- Text goes in quotes:
"East". Matching ignores capital letters. - A cell goes in without quotes:
F3. This is the usual choice, because changing the cell changes the result. - A comparison goes in quotes, with the operator first:
">100","<=50","<>East"(not East). - A comparison with a cell joins the operator and the cell with
&:">"&F4. Writing">F4"in quotes does not work: Excel then compares against the text "F4" and finds nothing. - Wildcards work with text:
*stands for any number of characters and?for exactly one, so"Lap*"matches Laptop.
A bare number such as 5 means "equal to 5". To match "greater than 5", use ">5". "<>" on its own means "not empty".
Worked example: a comparison from a cell
To add up the revenue of orders with more than the number of units in F4, test the Units column and add the Revenue column:
=SUMIF(C2:C11, ">"&F4, D2:D11)
With 5 in F4, ">5" is true for 10, 6, 8, 7 and 9 units, so SUMIF adds the revenue of those five orders. An order of exactly 5 is not included, because > means strictly greater. Use ">="&F4 to include it.
SUMIFS: more than one condition
SUMIF takes exactly one condition. When you need two or more, for example West and Phone, use SUMIFS. The argument order is different: the range to add comes first, then each condition as a pair of range and criteria.
=SUMIFS(D2:D11, A2:A11, "West", B2:B11, "Phone")
All conditions must be true in the same row. SUMIFS also works with a single condition, and many people use it all the time so they only have to remember one argument order. SUMIF is still handy when you want to read the formula as "range, criteria, sum range", and it lets you leave out the sum range.
Filling a summary table down and across
A summary table, with products down the side and regions across the top, needs one formula that you copy to every cell. Lock the three data ranges with $ so they stay put, and lock only the half of each criterion cell that must not move: $F10 keeps the column fixed while the row changes, and G$9 keeps the row fixed while the column changes.
Practise SUMIF and SUMIFS
Click a numbered cell, type a formula and press Enter. Each answer is checked straight away, then tested again on changed data, so the result has to come from the formula. While typing a formula, click or drag across cells to insert their references. Yellow cells are inputs you can change.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Units | Revenue | Criteria | Revenue | ||||
| 2 | East | Laptop | 3 | 2,400 | East | |||||
| 3 | West | Phone | 10 | 5,000 | Phone | |||||
| 4 | East | Phone | 6 | 3,000 | 5 | |||||
| 5 | North | Tablet | 4 | 1,200 | ||||||
| 6 | West | Laptop | 2 | 1,600 | Region | Product | Revenue | |||
| 7 | North | Phone | 8 | 4,000 | West | Phone | ||||
| 8 | East | Tablet | 5 | 1,500 | ||||||
| 9 | West | Tablet | 7 | 2,100 | Revenue | East | West | North | ||
| 10 | North | Laptop | 1 | 800 | Laptop | |||||
| 11 | East | Phone | 9 | 4,500 | Phone | |||||
| 12 | Tablet | |||||||||
| 13 | ||||||||||
| 14 |
-
1In
G2, add up the revenue (column D) of all orders from the"East"region (column A). Type the text"East"into the formula.Show answer
=SUMIF(A2:A11,"East",D2:D11) -
2In
G3, add up the revenue of the product named inF3. Point at the cell instead of typing the name, so the formula followsF3when you change it.Show answer
=SUMIF(B2:B11,F3,D2:D11) -
3In
G4, add up the revenue of orders with more units than the number inF4. The units are in column C and the revenue in column D.Show answer
=SUMIF(C2:C11,">"&F4,D2:D11) -
4In
H7, add up the revenue of orders from the region inF7and the product inG7. One condition is not enough here, so use SUMIFS.Show answer
=SUMIFS(D2:D11,A2:A11,F7,B2:B11,G7) -
5Write one formula in
G10that adds up the revenue for the product in column F and the region in row 9. It is filled across toI12for you, and all nine cells must be right.Show answer
=SUMIFS($D$2:$D$11,$B$2:$B$11,$F10,$A$2:$A$11,G$9)
Common SUMIF and SUMIFS problems
The result is 0 and you expected a number
Nothing matched. Check the spelling of the criteria and look for extra spaces in the data (TRIM removes them). A comparison written as ">F4" is a common cause: join it with & and write ">"&F4. Also check that the numbers are really numbers: a number stored as text does not match a numeric comparison.
#VALUE!
In SUMIFS, the sum range and all criteria ranges must have the same number of rows and columns. A range of 10 rows next to a range of 9 gives #VALUE!.
The result is too small or too large with SUMIFS
The argument order is probably the SUMIF one. In SUMIFS the sum range comes first. If you start with the criteria range, Excel tries to add up text and gets 0, or reports an error.
Right in the first cell, wrong after filling
The ranges moved. Lock the data ranges with dollar signs, for example $D$2:$D$11, and use $F10 or G$9 for criteria that should move in only one direction.
You need OR, not AND
SUMIFS only adds a row when every condition is true. To add rows where either of two conditions is true, add two SUMIFs together, as long as no row can match both. Or use SUM(SUMIFS(..., {"East","West"})).
Questions
What is the difference between SUMIF and SUMIFS?
SUMIF has one condition and takes the sum range last. SUMIFS has up to 127 conditions and takes the sum range first. SUMIFS also works with a single condition, so it can replace SUMIF everywhere.
How do I use a cell reference with ">" in SUMIF?
Join the operator and the cell with an ampersand: =SUMIF(C2:C11, ">"&F4, D2:D11). Writing ">F4" in quotes compares against the text "F4" and finds nothing.
How do I sum if a cell contains certain text?
Put wildcards around the text: =SUMIF(A2:A11, "*East*", D2:D11) adds up every row where column A contains East. A * matches any number of characters.
How do I sum between two numbers or dates?
Use SUMIFS with the same range twice: =SUMIFS(D2:D11, C2:C11, ">="&F1, C2:C11, "<="&F2). Both conditions must be true, so only values between the two limits are added.
Does SUMIF ignore capital letters?
Yes. "east", "East" and "EAST" all match each other. SUMIF cannot tell them apart.
Can SUMIF work on another sheet or a closed workbook?
It can read ranges on other sheets, for example Data!A2:A100. A range in another workbook only works while that workbook is open. When it is closed, SUMIF and SUMIFS both return #VALUE!. SUMPRODUCT does not have this limit.