SUMIF function
SUMIF adds up the values that match one condition, such as sales for a single region, and returns the total. For several conditions at once, use SUMIFS.
Example
Read it as a sentence: look down C2:C7 for Smoking and add the matching quantities from D2:D7.
How SUMIF works
SUMIF adds up only the rows that pass one test. What did the North region sell? How much went on travel? What do all the invoices over 1,000 come to?
It takes three things: the range to test, the condition, and the range to add. Excel checks each cell of the first range against the condition, and wherever one passes, it adds the cell in the same position of the third range.
In the example above, the test range is the Category column and the condition is Smoking. Three rows say Smoking: the briar pipe, the cigar and the clay pipe. Their quantities, 7, 5 and 15, add up to 27.
The condition can be text, a number, a comparison in quotes such as ">=5", or a cell. Leave out the third argument and SUMIF adds the tested cells themselves.
Syntax
SUMIF(range, criteria, [sum_range])
Arguments in [square brackets] are optional.
- Where to test
range(required) - The cells tested against the criteria.
- The condition
criteria(required) - The rule, such as "East", ">100" or "<>0".
- What to add
sum_range(optional) - The cells to add; if omitted, range itself is summed.
Returns: The total of cells meeting the single condition.
More examples
Each picture shows only the columns that formula reads.
-
Add the values above a number
What do the items worth 5 or more add up to?
Excel shows 16E1Unit Value2$3.503$4.254$2.005$10.006$1.757$6.00ItemsWith no third argument, the tested cells are the ones added. Two unit values are 5 or more, the cigar's 10 and the commonplace book's 6, and together they come to 16.
-
Take the number from a cell
What do the items worth more than the briar pipe add up to?
Excel shows 16E1Unit Value2$3.503$4.254$2.005$10.006$1.757$6.00ItemsKeep the operator in quotes and join the cell to it with &: ">"&Items!E3. E3 holds the briar pipe's 4.25, so this adds the values above 4.25, again 16. Change E3 and the total follows.
-
Match part of the text
How many pipes are in stock?
Excel shows 22BD1ItemQty2Boxer cartridge123Briar pipe74Candle205Cigar56Clay pipe157Commonplace book9ItemsPut the asterisk in front, as in *pipe, and the condition fits any item name with pipe at the end. The briar pipe and the clay pipe have 7 and 15, which makes 22.
-
Add by date
How much of the stock was bought from 2024 on?
Excel shows 37DH1QtyAcquired212455983744365420456635545699615451997943570ItemsBuild the date with DATE and join it to the operator. Three items were acquired on or after January 1, 2024, and their quantities add to 37.
-
Add everything except one category
How much of the stock is not smoking items?
Excel shows 41CD1CategoryQty2Tools123Smoking74Household205Smoking56Smoking157Papers9Items<> means not equal to. Everything outside Smoking adds up to 41, the 68 in these six rows less the 27 smoking items.
SUMIF or SUMIFS?
For one condition they give the same answer: SUMIFS also returns 27 for the smoking items. The difference is the order of the arguments. SUMIF puts the range to add last, and lets you leave it out. SUMIFS puts it first, then takes as many range and condition pairs as you need.
Switch to SUMIFS as soon as there is a second condition, such as smoking items owned by Holmes. Writing SUMIFS even for one condition keeps the argument order the same across a workbook.
Watch out for
A cell reference inside the quotes is read as text
Write ">Items!E3" and Excel compares each value with the text Items!E3, not with the number in that cell. Nothing passes and the answer is 0, with no error to warn you. Put only the operator in quotes and join the cell with &.
-
=SUMIF(Items!E2:E7,">Items!E3")0 -
=SUMIF(Items!E2:E7,">"&Items!E3)16
A short sum range gets stretched
Give sum_range a single cell, D2, and Excel still adds down as many rows as the test range has. The answer is 27, the same as with D2:D7. It works, but the formula hides what it adds, so give both ranges the same size.
-
=SUMIF(Items!C2:C7,"Smoking",Items!D2)27
Text conditions ignore case
smoking in lower case still finds Smoking. SUMIF can't tell upper case from lower case.
-
=SUMIF(Items!C2:C7,"smoking",Items!D2:D7)27
Two quotes match empty cells
A condition of "" matches cells with nothing in them. Two people have no Home on file, Moriarty and Wiggins, and their daily fees, 0 and 0.25, add to 0.25.
-
=SUMIF(People!D2:D16,"",People!F2:F16)0.25
An error in the sum range spreads
If one of the cells to be added holds #N/A, the whole SUMIF returns #N/A. An error in the test range doesn't do this: that cell fails the test and the rest still add up.
-
=SUMIF(Data!A1:A5,">0",Data!C1:C5)#N/A -
=SUMIF(Data!C1:C5,">1")11
Questions people ask
How do I write a SUMIF formula?
=SUMIF(range to test, condition, range to add). Put an operator and a number in quotes, as in ">=5", and join a cell to an operator with &.
-
=SUMIF(Items!E2:E7,">"&Items!E3)16
Can you use <> in SUMIF?
Yes. "<>Smoking" adds everything that is not Smoking, 41 here, and "<>" on its own means any cell that isn't empty.
-
=SUMIF(Items!C2:C7,"<>Smoking",Items!D2:D7)41 -
=SUMIF(Data!S1:S6,"<>")120
What is the difference between VLOOKUP and SUMIF?
VLOOKUP brings back one value from the first matching row: the quantity of the first Smoking item, 7. SUMIF adds across every matching row: the unit values of all three Smoking items come to 16.
-
=VLOOKUP("Smoking",Items!C2:E7,2,FALSE)7 -
=SUMIF(Items!C2:C7,"Smoking",Items!E2:E7)16
What is the SUMIFS formula in Excel?
SUMIFS adds the values that meet several conditions, with the range to add first. This one adds the quantity of smoking items worth 4 or more each and returns 12.
-
=SUMIFS(Items!D2:D7,Items!C2:C7,"Smoking",Items!E2:E7,">=4")12
Can SUMIF match TRUE and FALSE?
Yes. A condition of TRUE adds the rows where the Insured column is TRUE. The two insured items, the magnifying glass and the microscope, are worth 258.75.
-
=SUMIF(Items!I2:I13,TRUE,Items!E2:E13)258.75
Where it works
- Excel
- SUMIF is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Math and trig functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .