How to Use SUMIF in Excel With Simple Stock Examples

SUMIF answers the question how many of this thing, out of a long mixed list. How many mugs did we ship, how many hours did Sam log, how many units came back as returns. It's COUNTIF's sibling: COUNTIF counts the rows that match, SUMIF adds up a number from each matching row. Here's how it works with a simple stock example.

The three parts of a SUMIF formula

Part	What it means	In the example
range	Where Excel looks for your condition	A2:A10, the product names
criteria	What it's looking for	"Mugs"
sum_range	The numbers to add up	B2:B10, the quantities

Microsoft's syntax is SUMIF(range, criteria, [sum_range]). The square brackets mean sum_range is optional: leave it out and Excel adds up the cells in range itself, which only makes sense when range holds numbers.

Adding up quantities for one product

1. Put product names in column A and quantities shipped in column B, with headers in row 1.
2. Click an empty cell, such as E2.
3. Type =SUMIF(A2:A10, "Mugs", B2:B10) and press Enter.
4. Excel adds the quantity from every row where column A says Mugs.

Better still, put the product name in a cell. If D2 says Mugs, use =SUMIF(A2:A10, D2, B2:B10). Type Candles into D2 and the total updates. A short list of products in column D with a SUMIF beside each makes a tidy summary of your whole order log.

Conditions on numbers

SUMIF can also add up numbers based on their own size. Microsoft's example =SUMIF(B2:B25, ">5") adds only the values larger than 5. Some other conditions you can use:

• ">=3" for 3 or more.
• "<>Mugs" for every product except mugs.
• ">"&D2 to compare with a number in another cell.
• "" to add rows where the condition cell is blank, as Microsoft shows for items with no category.

Microsoft's rule on quote marks is worth memorising: text criteria, and anything with symbols like > or =, go in double quotes. A plain number, such as 5, doesn't need them.

Partial matches with wildcards

Product names are rarely perfectly consistent. Microsoft says you can use an asterisk for any run of characters and a question mark for a single character. =SUMIF(A2:A10, "*mug*", B2:B10) adds every row with mug anywhere in the name, so Blue mug and Mug, large both count. To match an actual asterisk or question mark, put a tilde (~) in front of it.

The mismatched range mistake

Here's the catch Microsoft calls out. Your range and sum_range should be the same size and shape. If you write =SUMIF(A2:A10, "Mugs", B2:B5), Excel doesn't give you an error. Instead it quietly sums a range that starts at B2 but is stretched to the size of A2:A10. The answer might look right, but it's easy to end up with a formula that doesn't do what you think, and Microsoft says performance can suffer too. Always make the two ranges match row for row.

Other reasons SUMIF returns the wrong total

• Quantities stored as text. Numbers typed with a stray apostrophe or imported as text are skipped. They usually sit on the left of the cell.
• Extra spaces in names. Mugs with a trailing space won't match "Mugs". Clean the column with TRIM, or use a wildcard.
• Very long text. Microsoft says SUMIF gives incorrect results when matching strings longer than 255 characters.
• Missing quote marks around text criteria.

When you need more than one condition

SUMIF handles one condition. For mugs shipped to Leeds, or hours logged by Sam on a Monday, use SUMIFS, which Microsoft points to for multiple criteria. Note the order changes: the range to add comes first. =SUMIFS(C2:C10, A2:A10, "Mugs", B2:B10, "Leeds") adds column C where A is Mugs and B is Leeds.

Turning totals into a simple dashboard

A column of product names with a SUMIF next to each is the start of a dashboard. If you'd like to go further, my guide to building an Excel dashboard shows how to turn totals like these into a one-page view. And when you want averages, such as units per order, divide your SUMIF by a COUNTIF; my guide to dividing in Excel explains how to avoid the divide-by-zero error when a product has no orders yet.

A worked example for attendance

SUMIF isn't only for stock. If you run workshops, list each booking in a row with the client's name in column A and the number of places booked in column B. =SUMIF(A2:A10, "Sarah Jones", B2:B10) tells you how many places Sarah has booked across every session. Add a column C for the session date and you can use SUMIFS to see places booked for one particular session. It's the same formula pattern, just pointed at a different list, which is why it's worth learning once and reusing everywhere, from stock to bookings to hours.

My take

SUMIF is the formula I'd teach anyone running a small business spreadsheet after SUM itself. Range, criteria, sum_range, same size ranges, quotes around text. Put the criteria in a cell so you can reuse it, and switch to SUMIFS the moment you need a second condition.

Frequently asked questions

What is the SUMIF formula in Excel?

=SUMIF(range, criteria, sum_range). Range is where Excel checks the condition, criteria is the condition, and sum_range holds the numbers to add.

Do I need quote marks in SUMIF?

Yes for text and for conditions with symbols, such as ">5". A plain number doesn't need them.

Can SUMIF use more than one condition?

No. Use SUMIFS for two or more conditions.

Why is my SUMIF returning zero?

The criteria may not match exactly because of spaces or spelling, or the numbers may be stored as text.

Can I use wildcards in SUMIF?

Yes. An asterisk matches any characters and a question mark matches one character.