Excel SUMIF Function

SUMIF adds up values in a range when they meet a condition you set, and returns the total as a single number. It only evaluates one condition — if a row needs to pass two or more tests before it counts, you're looking for SUMIFS instead.

Syntax

=SUMIF(range, criteria, [sum_range])

Parameter	Required	Description
`range`	Yes	The cells Excel checks against your condition. If you skip `sum_range`, this is also what gets summed.
`criteria`	Yes	The condition a cell in `range` must meet. Can be a number, text in quotes, a cell reference, or an expression like `">100"`.
`sum_range`	No	The cells to add up when the matching row in `range` meets `criteria`. If omitted, SUMIF just totals `range` itself.

⚠

range and sum_range should be the same size and shape. If they're not, Excel anchors sum_range to the top-left cell of range and stretches to match — which can pull numbers from rows you didn't intend to include. It won't throw an error. It'll just add the wrong cells.

Basic Example

You're tracking orders with the region in column A and the order amount in column B. To total every order from the West region:

=SUMIF(A2:A200, "West", B2:B200)

// A2:A200  — the range Excel checks for "West"
// "West"   — the criteria
// B2:B200  — the range that actually gets summed

SUMIF scans A2:A200 row by row. Every time it finds "West," it adds the corresponding value from B2:B200 to the running total. Rows where column A says "East" or "North" get skipped entirely.

How SUMIF Works

Criteria can be text, numbers, or expressions

A plain number like 100 matches cells equal to 100. Text needs quotes: "West". For anything beyond an exact match, use a comparison operator inside quotes: ">100", "<>0", ">=1/1/2026".

=SUMIF(commission_rate, ">0.05", sales)   // sum sales where commission rate exceeds 5%

Wildcards work in text criteria

* matches any number of characters, ? matches exactly one. Useful when you need a partial match instead of an exact one.

=SUMIF(products, "*Shirt*", revenue)   // matches "T-Shirt", "Dress Shirt", "Shirt - Blue"

Wildcards only work in text criteria. "*5*" won't match numbers containing a 5 — SUMIF compares those as numeric values, not as strings.

Blank cells in sum_range are treated as zero

SUMIF doesn't skip blanks or throw an error on them. A blank cell in sum_range just contributes nothing to the total, the same as if it held a 0.

It only handles one condition

SUMIF stops at a single test. Need "region = West AND month = March"? That's two conditions, and SUMIF can't do it. SUMIFS takes the same basic arguments but accepts as many condition pairs as you need.

Common Use Cases

Sum values above a threshold

Total every order over $500, without listing out which ones qualify:

=SUMIF(orders, ">500", order_total)

Sum by partial text match

Add up revenue for every product with "Pro" in the name, regardless of what comes before or after it:

=SUMIF(product_names, "*Pro*", revenue)

Exclude a specific value

Total everything except returns, for example:

=SUMIF(transaction_type, "<>Return", amount)

Sum based on a cell reference instead of a hardcoded value

Point criteria at a cell so the formula updates when that cell changes, instead of editing the formula itself every time:

=SUMIF(region, F1, sales)

// F1 holds the region name — change F1 and the total recalculates

Handling Errors

SUMIF rarely throws an actual error. The more common problem is a silent 0, which is harder to catch because Excel doesn't flag it.

Common causes of a 0 result:

criteria doesn't match anything in range — check for typos or extra spaces
Numbers stored as text in range, so "100" never matches the number 100
The wrong range was referenced, or the range doesn't cover all the data
sum_range points to an empty column

A 0 result isn't a thrown error, so IFERROR won't catch it or flag it. There's no formula wrapper that fixes this — check range, confirm criteria is spelled and formatted correctly, and verify the data types match, then re-test.

✓

If SUMIF returns 0 and you're sure the data is there, select the cells in range and check the alignment. Numbers stored as text left-align by default instead of right-align — that mismatch is usually the culprit.

Gotchas & Warnings

Why is SUMIF returning 0?

The most common cause is a text/number mismatch — your criteria is a number but the cells in range store that number as text, or vice versa. Trim any extra spaces with TRIM and confirm both sides are the same data type before assuming the formula is broken.

Why is SUMIF adding the wrong numbers?

Check that range and sum_range are identical in size. If range is A2:A200 and sum_range is B2:B150, Excel still tries to align them from the top, and the totals will be off. Always select matching row counts for both arguments.

Does SUMIF work with dates?

Yes. Dates are just numbers under the hood, so comparison operators work the same way: =SUMIF(order_dates, ">="&DATE(2026,1,1), order_total) sums everything from 2026 onward. Concatenate the operator and the date with & rather than typing the date directly into the criteria string.

Can SUMIF use multiple criteria?

No. SUMIF evaluates exactly one condition. For anything with two or more conditions — region AND month, category AND date range — use SUMIFS. It takes the same arguments as SUMIF, just repeated for each condition pair.

Related Functions

Function	Use this when...
SUMIFS	You need to sum values that meet two or more conditions.
COUNTIF	You want a count of matching cells instead of a sum.
Excel VLOOKUP	You need to pull a single matching value from a table rather than total a range.