Practice free

SUMIFS function

SUMIFS adds values where every condition is met, such as sales for one product in one quarter. All the conditions must be true, so for OR logic add several SUMIFS together.

Example

Read it as a sentence: add the quantities in D2:D7 on the rows where column C says Smoking and column E is 4 or more.

Practice this in the app

How SUMIFS works

SUMIFS adds the rows that pass every test you give it. How many tools does Holmes own? How many visits went to stations in London? How much stock arrived in 2025?

The range to add comes first. After it come pairs: a range to test, then the condition for that range. A row is added only when it passes every pair, so each extra pair narrows the total.

In the example above there are two pairs. Three rows of the Items sheet are Smoking, and two of those are worth 4 or more each: the briar pipe and the cigar. The clay pipe is only 1.75, so its 15 drops out of the smoking items' 27 and the answer is 12.

A condition might be a word, a number, a comparison like ">=5" in quotes, a wildcard pattern, or an operator joined to a cell with &. Every test range must match the size of the range to add.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments in [square brackets] are optional.

What to add sum_range (required)
The cells to add.
Where to test first criteria_range1 (required)
The first range to test.
The first condition criteria1 (required)
The rule for the first range.
[criteria_range2, criteria2], ... (optional)
Extra range and rule pairs; all must be true.

Returns: The total of cells where every condition is met.

More examples

Each picture shows only the columns that formula reads.

  1. Add values between two numbers

    How many units do we hold of things worth 2 to 6 each?

    Test the same column twice, once with ">=2" and once with "<=6". Both pairs must hold, so only values inside the band count. Four items fit: the boxer cartridge, briar pipe, candle and commonplace book. Their quantities, 12, 7, 20 and 9, make 48.

  2. Add values between two dates

    How much stock was bought during 2025?

    Join each operator to a DATE: on or after January 1, 2025, and before January 1, 2026. The candles and cigars were bought that year, and 20 and 5 make 25.

  3. Add either of two categories

    What do the tools and the smoking items add up to together?

    Put both categories in braces and SUMIFS works out one total for each: 12 for Tools, 27 for Smoking. SUM around the outside adds the two totals into one, 39.

  4. Leave out a pattern

    How many smoking items are there that are not pipes?

    "<>*pipe" means anything that does not end in pipe. That drops the briar and clay pipes and keeps the cigars, so the answer is 5.

  5. Use three conditions

    How many cheap smoking items does P01 own?

    Add a third pair after the second: unit value under 10. Of P01's smoking items only the briar pipe (7) and the clay pipe (15) cost less than 10. The cigar costs exactly 10, so it is out. That gives 22.

SUMIFS or SUMIF?

With one condition the two give the same total: both return 27 for the Smoking rows. What changes is where the range to add goes. In SUMIFS it is the first argument; in SUMIF it is the last one, and optional.

SUMIF stops at one condition. SUMIFS takes as many test pairs as the job needs, so a second condition never means rewriting the formula in a new order. That is a good reason to use SUMIFS even when you have one condition.

Watch out for

Two conditions on one column means both at once

Test column C for Tools and again for Smoking, and SUMIFS looks for rows that are both. No row is, so the total is 0. For either one, put the two words in braces inside a SUM, which gives 39.

  • =SUMIFS(Items!D2:D7,Items!C2:C7,"Tools",Items!C2:C7,"Smoking") 0
  • =SUM(SUMIFS(Items!D2:D7,Items!C2:C7,{"Tools","Smoking"})) 39

Every range must be the same size

End the test range one row short, at C6, and SUMIFS returns #VALUE! instead of a total. Check that every range runs from the same first row to the same last row.

  • =SUMIFS(Items!D2:D7,Items!C2:C6,"Smoking") #VALUE!

A list of conditions without SUM gives two answers

Braces with two categories make SUMIFS return one total per category. Without SUM around it, the formula spills into two cells, 12 and 27, rather than one.

  • =SUMIFS(Items!D2:D7,Items!C2:C7,{"Tools","Smoking"}) 12

No matching rows gives 0, not an error

Ask for a category that isn't there and SUMIFS returns 0. A misspelled condition looks the same as a real zero, so check the wording when a total is 0.

  • =SUMIFS(Items!D2:D7,Items!C2:C7,"Nothing") 0

TRUE and FALSE work as conditions

Test the Insured column with TRUE and SUMIFS adds the unit values of the insured rows. Over rows 2 to 13, the magnifying glass and the microscope come to 258.75.

  • =SUMIFS(Items!E2:E13,Items!I2:I13,TRUE) 258.75

Questions people ask

How do I use SUMIFS in Excel?

Start with the range to add, then give each test as a range followed by its condition. The example on this page totals the quantity of smoking items worth 4 or more each: 12.

  • =SUMIFS(Items!D2:D7,Items!C2:C7,"Smoking",Items!E2:E7,">=4") 12
What does the SUMIFS function do?

It adds the values in one range for the rows where every condition holds. When no row passes, it returns 0.

  • =SUMIFS(Items!D2:D7,Items!C2:C7,"Nothing") 0
How do I use SUMIFS with 3 criteria?

Add a third range and condition after the second pair. Smoking, owned by P01 and worth under 10 gives 22.

  • =SUMIFS(Items!D2:D7,Items!C2:C7,"Smoking",Items!F2:F7,"P01",Items!E2:E7,"<10") 22
Should I use XLOOKUP or SUMIFS?

XLOOKUP returns a single value, taken from the first row that matches. Use SUMIFS to total every matching row: the two London stations, Paddington and Waterloo, have 31 visits between them.

  • =SUMIFS(Places!G2:G16,Places!D2:D16,"London",Places!C2:C16,"Station") 31

Where it works

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