Featured image
SUMIF in Excel
SUMIF adds up only the rows that match one condition: =SUMIF(range, criteria, [sum_range]). The range is what gets tested, the sum_range is what gets added, and they are different arguments because they are usually different columns. A comparison goes inside quotation marks, as ">10", because the operator and the number are handed over together.
=SUMIF(range, criteria, [sum_range])
SUMIF is the first function most people meet that tests something before doing arithmetic with it, and it is where two small pieces of syntax cause most of the trouble. The first is that the column being tested and the column being added are separate arguments, in that order, which is the opposite of SUMIFS. The second is that a criterion like greater than ten is written as a piece of text.
Both are easier to remember once you see why. SUMIF reads as a sentence: look through this range, for this criterion, and add up the matching rows of that range. SUMIFS cannot read that way, because it has an unlimited number of criteria and the thing being added has to come first so the pairs that follow can line up.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- The cells to test. Usually a column of categories: region, department, status.
- What counts as a match. Plain text or a number matches exactly, "North" or 12. A comparison is written as text with the operator inside the quotes, ">10" or "<>North". Wildcards work: "A*" matches anything beginning with A.
- optional
- The cells to add. Leave it off and SUMIF adds up the range it tested, which is what you want when the test and the total are the same column, as in sum everything over 5000.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
Total one category
=SUMIF(B2:B7, "North", D2:D7)Returns 12400
Test the region column for North, add the revenue column for the rows that matched. Ada's 4800 and Chen's 7600. The two ranges must be the same height, and the match is not case-sensitive, so "north" gives the same answer.
Total where a number passes a test
=SUMIF(C2:C7, ">10", D2:D7)Returns 17650
The criterion is a comparison, so it goes in quotes as a whole: the operator and the number travel together. Three reps sold more than ten units, and their revenue adds up to 17650.
One column, tested and totalled
=SUMIF(D2:D7, ">5000")Returns 12850
With the third argument left off, SUMIF adds up the same cells it tested. This is the shortest correct form of the function and the only time the third argument should be missing.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
Writing criteria
- Exact text: "North". Quotes required, capitals ignored.
- A number: 12. No quotes needed, though quotes do no harm.
- A comparison: ">10", "<=100", "<>North". The operator goes inside the quotes with the value.
- A comparison against a cell: ">"&C2. The ampersand joins the operator to whatever C2 holds, which is how a threshold becomes something a reader can change without editing a formula.
- Wildcards: "A*" for anything beginning with A, "?at" for a three-letter word ending in at.
=SUMIF(C2:C7, ">"&C2, D2:D7)Returns 12850. Greater than whatever is in C2, which is 12. Building the criterion with & is what makes a report respond to a cell instead of needing the formula rewritten.
SUMIF and SUMIFS take their arguments in opposite orders
This is not a trick question and there is no way to reason your way out of it. The two functions genuinely disagree, and knowing that is most of the battle.
- SUMIF(range to test, criteria, range to add). The column being added comes last.
- SUMIFS(range to add, range to test, criteria, …). The column being added comes first.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=SUMIF(B2:B7, "North", D2:D7) + "x"WhySUMIF rarely errors on its own. A #VALUE! around it usually comes from the arithmetic it sits inside rather than the function.
FixEvaluate the SUMIF alone in a spare cell. If that is a number, the fault is in what surrounds it.
=SUMIF(B2:B7, "North", D2:D7)/COUNTIF(B2:B7, "West")WhyA hand-built average: total the matching rows, divide by how many there were. There is no West region, so the count is zero and the division fails.
FixThis is the error worth keeping rather than hiding, because it says plainly that nothing matched. If a blank is the right answer for an empty category, wrap it: IFERROR(that, ""). AVERAGEIF does the same job in one function and returns #DIV/0! for the same reason.
What goes wrong
These are the mistakes that come up again and again with SUMIF. Each one is reasonable. That is why people make it.
Getting a zero and assuming the formula is broken.
WhyZero looks like a failure, and SUMIF has no error to offer when nothing matched.
InsteadZero means no rows matched. Check the criterion against what is really in the column: a trailing space, or "North " against "North", produces a confident zero. COUNTIF with the same criterion tells you how many rows the test found.
Writing the comparison outside the quotes, as >"10".
WhyIt looks like how a comparison is written everywhere else in Excel.
InsteadThe whole criterion is one piece of text: ">10". Against a cell it is built with &, as ">"&C2.
Leaving the quotes off a text criterion.
WhyNothing about the word North suggests it needs dressing up.
InsteadQuote it: "North". Excel answers this with #NAME?; the grid here answers it with a confident zero, which is the same mistake and harder to spot. A number needs no quotes; a comparison needs them, with the operator inside.
Ranges of different heights.
WhyThe two ranges are selected in two separate drags.
InsteadB2:B7 and D2:D7, not B2:B7 and D2:D20. A mismatch produces an answer rather than an error in some versions, which is worse than failing.
Forgetting to lock the ranges before copying down.
WhyThe first row is right, so the formula looks finished.
InsteadMake both ranges absolute, $B$2:$B$7 and $D$2:$D$7, and leave the criterion relative if it points at a cell. Otherwise every row tests a window that has slid one row further down.
Common questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the column being added last. SUMIFS takes any number of conditions and puts the column being added first. SUMIFS can do everything SUMIF does, so using it everywhere avoids the argument-order confusion entirely.
How do I write greater than in a SUMIF?
As text, with the operator inside the quotes: ">10". To compare against a cell, join them with an ampersand: ">"&C2.
Why does my SUMIF return 0?
Nothing matched. The usual causes are a trailing space on one side, a number stored as text in the tested column, or a criterion that is subtly different from what the data holds. Run COUNTIF with the same criterion: if it also returns 0, the problem is the criterion rather than the sum.
Is SUMIF case sensitive?
No. "north" and "North" match the same rows. SUMPRODUCT with EXACT is the usual workaround when capitalisation has to matter.
Can SUMIF use two conditions?
No. That is what SUMIFS is for.