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.
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.
-
Add values between two numbers
How many units do we hold of things worth 2 to 6 each?
Excel shows 48DE1QtyUnit Value212$3.5037$4.25420$2.0055$10.00615$1.7579$6.00ItemsTest 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.
-
Add values between two dates
How much stock was bought during 2025?
Excel shows 25DH1QtyAcquired212455983744365420456635545699615451997943570ItemsJoin 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.
-
Add either of two categories
What do the tools and the smoking items add up to together?
Excel shows 39CD1CategoryQty2Tools123Smoking74Household205Smoking56Smoking157Papers9ItemsPut 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.
-
Leave out a pattern
How many smoking items are there that are not pipes?
Excel shows 5BCD1ItemCategoryQty2Boxer cartridgeTools123Briar pipeSmoking74CandleHousehold205CigarSmoking56Clay pipeSmoking157Commonplace bookPapers9Items"<>*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.
-
Use three conditions
How many cheap smoking items does P01 own?
Excel shows 22CDEF1CategoryQtyUnit ValueOwner2Tools12$3.50P013Smoking7$4.25P014Household20$2.00P035Smoking5$10.00P016Smoking15$1.75P017Papers9$6.00P01ItemsAdd 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 .