Excel SUMIFS examples: multiple criteria, date ranges, and what to do when it isn't working

What does SUMIFS do in Excel?

SUMIFS adds up the numbers from the rows that meet all your conditions. Where SUMIF asks one question, SUMIFS asks as many as you like: is it clothing, and did Sophie sell it? =SUMIFS(C2:C16,B2:B16,"Clothing",E2:E16,"Sophie") only adds up the prices where both hold. In a Dutch copy of Excel, the same function is called SOMMEN.ALS.

Every example works on the same sheet: a week of sales at the Second Chance charity shop in Gloucester, the fourth client in our Excel course. If you'd rather learn the function step by step, follow the lesson on multiple conditions. If SUMIF is new to you, start there, because the criteria work exactly the same way. This article is the reference for the step to more than one condition.

How do you write a SUMIFS formula?

First comes what you're adding up, then the conditions follow in pairs. The first pair is required, the others are optional, and you can use up to 127 (Microsoft).

=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)

With Dutch or Belgian regional settings you'd write =SOMMEN.ALS(C2:C16;B2:B16;"Clothing";E2:E16;"Sophie"), with semicolons instead of commas. If you copy a formula from a site in another language, translate both the function name and the separators. On the course pages you choose which form you see.

How do you sum with two conditions?

Coordinator Yasmina at Second Chance wants to know how much clothing Sophie sold. This is the whole sheet, with all fifteen sales of the week. G2 holds a price limit of £10, which we'll refer to in a moment.

=SUMIFS(C2:C16,B2:B16,"Clothing",E2:E16,"Sophie")

The formula returns £19. Excel looks at each row and tests both conditions on that row. Clothing appears six times, but only Dress green, T-shirt XL and T-shirt M are also Sophie's: 12 + 4 + 3 = 19. Sophie's lamp drops out on its category, the black dress on its volunteer. A row only counts when every condition holds.

Try it yourself. The exercise runs on Second Chance's real sheet, and you type the formula as you would in Excel, with commas or semicolons.

Your formula ran in your own browser, on a real calculation engine. We check the result, not the letters: then we run your formula again on a hidden version of the sheet, with other prices and days. Anyone who types 16 gets caught there.

How do you use greater than and a cell in SUMIFS?

The same way as in SUMIF: the operator goes in quotation marks, and you join a cell on with &. Criteria can take any form you know from the lesson on criteria, and you can mix them. The range you test can even be the sum range itself, so you test on the price and add up that same price.

Wildcards work too: ? stands for one character, * for a run of characters, and a ~ in front turns them back into a plain question mark or asterisk (Microsoft). When you refer to a cell, the formula follows as soon as someone changes the limit. Try that below. The hidden version of the sheet has a different limit, so a typed 10 stands out.

How do you sum between two values or two dates?

Use the same range twice, with a lower limit and an upper limit. =SUMIFS(C2:C16,C2:C16,">=5",C2:C16,"<=10") adds up the prices from £5 to £10 inclusive: £35, for the lamp, the gown, the game, the vase and the bookend. That only works in SUMIFS, because SUMIF has a single criteria.

A date range works the same way, because a date is a number to Excel. Second Chance writes the day as text, but with a real date in column D you'd write this for all of November:

=SUMIFS(C2:C16,D2:D16,">="&DATE(2026,11,1),D2:D16,"<"&DATE(2026,12,1))

Test for less than 1 December rather than less than or equal to 30 November. That way, a sale on 30 November at 3 pm still counts, because it falls just after the date of 30 November. If the start and end dates are in cells, it becomes ">="&H1 and "<"&H2+1, with H1 and H2 as an example. Yasmina can then pick the period herself without touching the formula.

Can SUMIFS work with OR?

Not with ordinary criteria: all the conditions in one SUMIFS must hold at the same time. That's why =SUMIFS(C2:C16,B2:B16,"Clothing",B2:B16,"Books") returns £0. No single cell is clothing and books at once. It's the most common wrong assumption about this function, and it gives no error message, just a zero.

For one thing or another, add two formulas together. =SUMIFS(C2:C16,B2:B16,"Clothing")+SUMIFS(C2:C16,B2:B16,"Books") returns £51: £46 for the clothing and £5 for the books. That's correct because a row has only one category. Ask for clothing or Sophie's sales, though, and one row can meet both. Sophie's £19 of clothing then counts twice, so you subtract it again.

Why isn't my SUMIFS working?

SUMIFS is stricter than SUMIF, which is lucky: you see some of the mistakes straight away. The rest give a believable wrong amount or a silent zero. These are the causes you'll run into most, each with its fix.

The formula returns #VALUE!

Usually the ranges aren't the same size. Criteria ranges and the sum range must refer to the same number of rows and columns, or SUMIFS returns #VALUE! (Microsoft). =SUMIFS(C2:C16,B2:B16,"Clothing",E2:E15,"Sophie") stops there, because E2:E15 is a row short. SUMIF is more forgiving (Microsoft), which makes a shifted range harder to spot.

The sum range is at the end

In SUMIF the sum range is the third argument, in SUMIFS the first, and Microsoft points this out itself (Microsoft). Anyone who writes =SUMIFS(B2:B16,"Clothing",C2:C16) gives the function criteria without a range of their own, and Excel won't accept that.

When you turn a SUMIF into a SUMIFS to add a condition, move the sum range to the front first, and only then add the new pair.

The result is 0

First check whether you're asking for two conditions on one column that can't hold together, as above. Then look for text without quotation marks, which Microsoft names as the first cause of an unexpected 0 (Microsoft). Spaces in the cells and ">G2" instead of ">"&G2 also give a zero. They're all worked through in the SUMIF troubleshooting section.

Which functions are like SUMIFS?

SUMIFS has two close relatives, and all three read their pairs the same way. Once you know one, you can write the others without much thought.

If you want to count rather than add up, read COUNTIFS with examples. Averages with conditions are covered in AVERAGEIF and AVERAGEIFS. For the formulas you'll need most often, see the 12 most useful Excel formulas, and every function with its Dutch and English name is on the Excel cheat sheet.

If you want SUMIFS at your fingertips, follow module 4 of the Excel course. It's six short lessons on Second Chance's sheet, from COUNTIF to AVERAGEIF, and the first modules are free.