How to Use SUMIF in Excel:
the complete guide

What is SUMIF?

Imagine you have a list of all your expenses for the month — groceries, rent, eating out, transport. You want to know how much you spent on food only. You could filter the list and use SUM, or you could write one formula: SUMIF.

SUMIF adds up numbers that meet a condition you set. It's like saying: "only add the numbers where the category says Groceries."

It's one of the most used formulas in Excel — and once you learn it, you'll use it constantly.

The syntax explained

Three parts — here's what each one means:

• range — the column you want to check the condition against (e.g. the Category column)
• criteria — the condition to match (e.g. "Groceries")
• sum_range — the column with the numbers to add up (e.g. the Amount column). Optional — if you leave it out, Excel sums the range column itself.

8 real examples

1. Sum sales for one product

You have a sales log and want to total only the sales for "Coffee".

2. Sum values above a threshold

Total all expenses over $100 — useful for spotting big spends.

Note: when the criteria is a comparison like >100, you still put it in quotes but don't add a third argument — SUMIF uses the same range for checking and summing.

3. Sum values below a number

4. Reference a cell instead of typing the criteria

This is much more flexible — change the cell and the SUMIF updates automatically.

5. Wildcard — sum anything containing a word

Use * as a wildcard to match any text that contains a word.

6. Sum values that are NOT equal to something

7. Sum by month

If column A has dates and you want to sum a specific month:

8. Sum the top earners

Sum all values greater than the average:

SUMIFS — when you need multiple conditions

SUMIF handles one condition. When you need two or more, use SUMIFS instead.

Example: total Coffee sales in the North region only:

Common mistakes

Now practise it yourself

Reading about SUMIF is one thing. Actually typing it in a live spreadsheet is how you'll remember it. ExcelPro has 8 SUMIF exercises built from real scenarios — you type the formula, get instant feedback, and move on when you're right.