Excel SUMIF examples: text, dates, wildcards, and what to do when it isn't working

What does SUMIF do in Excel?

SUMIF adds up the numbers from the rows that meet one condition. Where SUM adds up everything, SUMIF first checks whether a row fits: is it clothing, does it cost more than £10, did Grace sell it? =SUMIF(B2:B16,"Clothing",C2:C16) looks for Clothing in column B and adds up the prices in column C for those rows. In a Dutch copy of Excel, the same function is called SOM.ALS.

Every example below 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, with explanations on the sheet itself, follow the lesson on SUMIF. This article is the reference that goes with it: an example for every kind of criteria, and what to do when the answer looks wrong.

How do you write a SUMIF formula?

SUMIF takes three arguments, and you can leave the last one out (Microsoft). The first says where Excel tests, the second what it tests for, and the third what it adds up. Here's the syntax as Microsoft's help writes it, with square brackets around the optional argument:

=SUMIF(range,criteria,[sum_range])

Most English-language help writes SUMIF with commas, but that comes from your settings, not from Excel. Microsoft says the separator depends on your operating system's locale and your Excel settings, and that the comma and the semicolon are the most common (Microsoft). Where the comma is the decimal mark, as in much of mainland Europe, you'll see semicolons instead.

Leave the sum range out and SUMIF adds up the cells it tests. That's handy when you test the numbers themselves: =SUMIF(C2:C16,">10") adds up every price over £10. When you test text, though, you need the third argument. Without it, the function adds up a column of words, and you get 0.

How do you sum by category with SUMIF?

Yasmina, who runs the volunteers at Second Chance, wants to know what the clothing brought in this week. Here's the whole sheet, all fifteen sales. At the till, the volunteers note the item, its category, the price, the day and who sold it, so Yasmina can work out afterwards what each kind of item brought in.

=SUMIF(B2:B16,"Clothing",C2:C16)

The formula returns £46. SUMIF works down column B and finds Clothing in six rows: Dress green, T-shirt S, Dressing gown, T-shirt XL, Dress black and T-shirt M. For each of those rows it takes the price in the same position in column C: 12 + 3 + 6 + 4 + 18 + 3 = 46. That's why the range and the sum range have to be the same size and start on the same row.

Now try it yourself, with a different condition. The exercise runs on Second Chance's real sheet, and you type the formula just as you would in Excel, with commas or semicolons. First work out which column you test in, and which column you add up.

Your formula ran in your own browser, on a real calculation engine. We check the result, not the letters of your formula: write "omar" in lower case and that's just as good. Then we run your formula again on a hidden version of the sheet with different prices. Work the amount out by hand and type it in, and it gets caught there.

How do you use greater than and less than in SUMIF?

Put the operator at the front of the criteria, inside the quotation marks: ">10", not >"10". Microsoft says any criteria with text or with logical or mathematical symbols must go in double quotation marks (Microsoft). Most of these examples test the prices and add them up too, so the sum range can go.

Watch the boundaries. The £15 radio counts with ">=15" but not with ">15", and that's the whole gap between 68 and 53. The same goes for the £5 game with "<5" and "<=5". With a plain number you can drop the equals sign and the quotation marks: 3, "3" and "=3" all return £9, for the three items at £3.

How do you use a cell reference in SUMIF criteria?

Join the operator and the cell with &, as in =SUMIF(C2:C16,">"&G2). The operator goes in quotation marks, the reference doesn't. With the £10 limit in G2 that returns £80, and when Yasmina changes the limit, the formula follows straight away. Microsoft uses the same pattern in its own examples (Microsoft).

The classic mistake is ">G2". Inside quotation marks, G2 is just text, so Excel looks for prices greater than the letters G2. There aren't any, so you get 0, with no error to warn you. Try it yourself below. We also check your formula on a hidden version of the sheet with a different limit, so a typed-in 10 stands out at once.

How does SUMIF work with text and wildcards?

Text criteria go in quotation marks, and case doesn't matter: "clothing" and "CLOTHING" also return £46. Microsoft says criteria aren't case sensitive (Microsoft). Forget the quotation marks and Excel reads Clothing as a name it doesn't know, which can show #NAME? (Microsoft).

For part of a text, use wildcards: * stands for any number of characters, including none, and ? for exactly one. To find a real question mark or asterisk, put a tilde ~ in front of it (Microsoft). The item names in column A are made for it:

=SUMIF(A2:A16,"Dress*",C2:C16)

Mind the space in "Dress *": it's part of the criteria, so the dressing gown drops out. Empty cells have criteria too. "" adds up where a cell is empty, "<>" where it isn't. =SUMIF(E2:E16,"",C2:C16) returns 0, because every sale has a name, and with "<>" you get all £134. If someone forgets to write their name, the first formula shows it straight away.

How do you use SUMIF with dates?

With the same operators as numbers, because to Excel a date is a number. Microsoft even lists TODAY() as possible criteria (Microsoft). Second Chance writes the day as text, so =SUMIF(D2:D16,"Sat",C2:C16) does the job there: £59 on Saturday. If column D held real dates, you'd write something like this:

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

The first formula adds up everything from 1 November 2026, the second everything before today. Between two dates you need two conditions on the same column, and SUMIF can't do that, so you use SUMIFS. Test for less than 1 December rather than less than or equal to 30 November, and a sale at 3 pm on 30 November still counts.

Why is my SUMIF not working?

Most of the time SUMIF doesn't show an error, just a wrong amount, and often that amount is 0. That's what makes these mistakes sneaky: the sheet looks finished. Here are the causes you'll run into most often, each with its fix, and all on the Second Chance sheet.

SUMIF returns 0

Look at the criteria first. "Clothing " with a trailing space matches nothing, and neither does ">G2". If the space is in the cells themselves, you won't even see it. Microsoft names leading and trailing spaces, and a mix of straight and curly quotation marks, as causes of unexpected results (Microsoft). Clean those cells with TRIM. And check the sum range is there: =SUMIF(B2:B16,"Household") adds up column B itself, which is 0.

Numbers stored as text

SUMIF ignores blank cells and text (Microsoft), including text that looks like a number. If the 12 for Dress green is stored as text in C2, the clothing formula returns £34 instead of £46. You'll spot such a cell by its left alignment and often a small green triangle in the corner. Choose Convert to Number there, or set the format to General and confirm the cell again with F2 and Enter.

Excel won't accept the formula

Then there's usually an operator outside the quotation marks, as in =SUMIF(C2:C16,>10), or a semicolon where your Excel expects a comma. If you copy a formula from a European colleague or forum, translate two things: the separators and the function name. =SOM.ALS(C2:C16;">10") becomes =SUMIF(C2:C16,">10"). A Dutch name in an English copy of Excel shows #NAME?.

The sum range is shifted or shorter

SUMIF doesn't check that the sum range is the same size as the range. If it isn't, Excel starts at the first cell of the sum range and takes a block the same size as the range (Microsoft). So =SUMIF(B2:B16,"Clothing",C3:C16) adds up, for every clothing row, the price of the row below it: £44 instead of £46.

A sum range that's one row out doesn't give an error, it gives a believable wrong amount. Always start and end the range and the sum range on the same rows.

SUMIF puts the sum range last, SUMIFS first

In SUMIF the sum range is the third argument, in SUMIFS the first, and Microsoft points this out itself (Microsoft). So when you turn a SUMIF into a SUMIFS to add a condition, the sum range moves to the front. =SUMIFS(C2:C16,B2:B16,"Clothing") returns the same £46 as the example at the top.

Can SUMIF handle multiple criteria?

No, SUMIF takes one condition. When two conditions must both be true, use SUMIFS: =SUMIFS(C2:C16,B2:B16,"Clothing",E2:E16,"Sophie") returns £19, the clothing Sophie sold. SUMIFS takes up to 127 pairs of a range and its criteria (Microsoft). You'll learn to build them in the lesson on SUMIFS.

When either condition will do, add two SUMIFs together. =SUMIF(B2:B16,"Clothing",C2:C16)+SUMIF(B2:B16,"Books",C2:C16) returns £51: 46 for the clothing and 5 for the books. That works because a row only has one category. Add up the clothing and Sophie's sales the same way, though, and you get £76 instead of £57: the £19 of clothing Sophie sold is counted twice.

Which functions are like SUMIF?

SUMIF has a whole family, and every member reads its criteria the same way: the same operators, the same wildcards, the same & for a cell. Know one and you can write the others without much thought. Here they are on the Second Chance sheet, each one for the clothing:

If you want to know how many items were sold rather than how many pounds, read the article on COUNTIF. Averages, with and without a condition, are covered in the article on AVERAGE. And if you want to decide something on every row rather than make a total, such as Pricey or Bargain next to each price, you need the IF function.

For the formulas you'll use most, see 12 basic Excel formulas everyone should know, and the Excel cheat sheet lists every function in the course with its English and Dutch name. To really get SUMIF under your fingers, take module 4 of the course: six short lessons on the Second Chance sheet, from COUNTIF to AVERAGEIF. Excel practice exercises with explanations shows what else there is to practise.