Practice free

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.

Practice this in the app

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.

  1. Add the values above a number

    What do the items worth 5 or more add up to?

    With 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.

  2. Take the number from a cell

    What do the items worth more than the briar pipe add up to?

    Keep 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.

  3. Match part of the text

    How many pipes are in stock?

    Put 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.

  4. Add by date

    How much of the stock was bought from 2024 on?

    Build 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.

  5. Add everything except one category

    How much of the stock is not smoking items?

    <> 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 .