Excel SUMIF: Syntax, Examples & Common Mistakes

What Does SUMIF Do?

The SUMIF function adds values in one column based on a condition in another. You tell it where to look, what to look for, and what to add up. It returns a single number — the total of all matching cells.

Here's the full syntax: =SUMIF(range, criteria, [sum_range])

The three arguments break down like this:

• range — the column you're testing against your condition (e.g., a list of regions or product names)
• criteria — the value, text, or expression that must be matched (e.g., "East" or ">500")
• sum_range — optional. The column containing the numbers you want to add. If you skip it, Excel sums the range itself.

A practical example: you have sales data with regions in column A and amounts in column B. To total only East sales: =SUMIF(A:A,"East",B:B). Excel checks every cell in A, finds the ones that say "East", and adds the corresponding B values.

If no cells match the criteria, SUMIF returns 0 — not an error. That's worth knowing when you're debugging a formula that seems to return nothing.

Text Criteria: Exact Match and Case Sensitivity

The most common use of SUMIF is matching text. You put the text in quotes inside the formula: =SUMIF(A:A,"East",B:B). Excel finds every cell in A that contains exactly "East" and sums the matching B cells.

One thing you don't need to worry about: case. SUMIF is case-insensitive, so "East", "EAST", and "east" all match the same way. If you need a case-sensitive sum, you'd need an array formula with EXACT() — but SUMIF alone won't do it.

You can also reference a cell instead of hardcoding the text. If D1 contains "East", both of these work identically: =SUMIF(A:A,"East",B:B) and =SUMIF(A:A,D1,B:B). The cell reference version is usually better — it makes your formulas easier to update when criteria change.

What about partial matches? That's where wildcards come in. By default, SUMIF requires an exact match — so "East" won't match "East Region" or "Northeast". You'll need the * wildcard for that. See the IF function in Excel for related conditional logic that complements SUMIF.

Wildcard Criteria: Partial Text Matches

When you need to match part of a cell's text, wildcards are your tool. The * character matches any number of characters in any position. So "*East*" catches "East", "East Region", "Northeast", and anything else containing the word East — wherever it appears in the cell.

The ? wildcard is more precise — it matches exactly one character. "Ea?t" matches "East" but not "Easxt" because it expects exactly one character between "Ea" and "t". This is useful for matching patterns with consistent lengths, like product codes that follow a fixed format.

You can combine wildcards freely. "A?-*" would match "A1-Widget", "A2-Gadget", or any two-character code starting with A followed by a hyphen and anything after it. The flexibility is real — you can get quite specific about patterns without writing complex formulas.

If you actually have asterisks or question marks in your data (not uncommon in product codes or financial models), use the tilde ~ to escape them. "~*" as the criteria finds cells with a literal asterisk — not a wildcard match. Similarly, "~?" finds cells containing a real question mark.

Wildcards only work with text. They have no effect on number or date comparisons. You also can't combine wildcards with operator prefixes like > — those two systems don't mix inside a single SUMIF criteria argument.

Number Criteria: Comparisons and Operators

Summing by a number condition works a little differently from text. When you use a comparison operator — greater than, less than, not equal to — you must wrap the entire expression in quotes: ">1000", "<500", "<>0".

If you're matching an exact number without any operator, no quotes are needed: =SUMIF(B:B,100,C:C) finds rows where B equals exactly 100 and sums C. But the moment you add >, <, or <>, the whole thing goes in quotes.

To combine an operator with a cell reference, use the & operator to concatenate: =SUMIF(B:B,">"&D1,C:C). Here D1 might contain 1000, and the formula builds the criteria string ">1000" on the fly. This is cleaner than hardcoding numbers you might need to change later.

The SUM formula in Excel is simpler but can't apply conditions — SUMIF fills that gap whenever you need conditional totals.

Summing Between Two Values

Here's something that surprises a lot of people: there's no way to do a "between" range with a single SUMIF. You can't say "sum values between 500 and 1000" in one formula — you need to subtract two SUMIF results.

The trick is: sum everything above the lower bound, then subtract everything above the upper bound. What's left is exactly the "between" portion: =SUMIF(B:B,">"&low,C:C)-SUMIF(B:B,">"&high,C:C)

Say you want to sum sales between $500 and $1,000. Put 500 in cell E1 and 1000 in E2, then write: =SUMIF(B:B,">"&E1,C:C)-SUMIF(B:B,">"&E2,C:C)

The first part adds everything above 500. The second part adds everything above 1000. Subtract the second from the first and you're left with just the 500–1000 range. It's a simple workaround once you know it. Alternatively, if you have multiple conditions to apply at once, switch to SUMIFS — more on that below.

Date Criteria: Summing by Date Range

Dates in Excel are stored as numbers, which means SUMIF can compare them with operators just like regular numbers. The tricky part is how you specify the date in the criteria.

The safest approach is to use the DATE() function instead of typing a date as text. Text dates are fragile — their interpretation depends on your regional settings. DATE(2024,1,1) is unambiguous regardless of where or which version of Excel you're running.

To sum all amounts where the date in column A is after January 1, 2024: =SUMIF(A:A,">"&DATE(2024,1,1),B:B)

Notice the concatenation pattern — you build the criteria string by joining the ">" operator with the DATE() result using &. This is the same pattern you'd use with a cell reference containing a date value. If D1 holds a date, the formula becomes =SUMIF(A:A,">"&D1,B:B).

For a date range — say January 2024 only — subtract two SUMIF calls: one for "after Dec 31, 2023" minus one for "after Jan 31, 2024". You'll get exactly the January entries. Same subtract-two-results pattern as the between-two-values approach covered earlier. It's not elegant, but it works reliably and is easy to audit.

One more tip: if your date column contains date-time values (timestamps), a date comparison like ">"&DATE(2024,1,1) still works — it compares the full serial number, so anything after midnight on Jan 1 qualifies.

Blank and Non-Blank Cells

Filtering by blank or non-blank cells is something that trips people up. The syntax is simple once you know it, but it's not obvious at first glance.

To sum values where the corresponding cell in another column is blank: =SUMIF(A:A,"",B:B) — two double quotes with nothing between them. Excel reads this as "match cells that contain nothing."

To sum where the corresponding cell is not blank: =SUMIF(A:A,"<>",B:B) — the not-equal operator with nothing after it. Any cell containing text, a number, or a date qualifies as non-blank.

This is useful when your data has optional fields. If column A holds a category label and some rows are uncategorized (blank), you can sum the uncategorized amounts with the blank criteria, and sum everything that's been categorized with the non-blank criteria. Together they let you quickly verify: do the two groups add up to the grand total?

One nuance worth knowing: cells containing an empty string returned by a formula — like =IF(condition,"",value) — are treated as non-blank by some Excel operations. But SUMIF with "" catches formula-driven empty strings too, so they'll show up in your blank sum. If that's not what you want, you may need to use SUMPRODUCT with ISBLANK() for stricter control.

SUMIF With a Separate Sum Range

When the column you're testing is different from the column you're adding, you need the third argument. Most real-world SUMIF formulas use all three arguments — that's the norm, not the exception.

Example: column A has product categories, column B has sales amounts. You want East totals — but you're testing column A and summing column B: =SUMIF(A:A,"East",B:B). The range (A:A) is where Excel looks for "East". The sum_range (B:B) is where the numbers live that get added up.

The third argument (sum_range) tells Excel where the numbers are. If your ranges have different sizes, Excel will flag a #VALUE! error or silently return wrong results — so keep them the same length. Using full column references like A:A and B:B sidesteps this problem entirely.

When you omit the third argument, SUMIF sums the range itself. So =SUMIF(B:B,">1000") adds up every cell in B that's greater than 1000 — testing and summing the same column. This works cleanly when the condition and the sum are on the same data. You can also use this alongside the COUNTIF function in Excel when you need both a count and a sum of the same filtered set — two functions, same ranges, complementary results.

One practical tip: if you're copying a SUMIF formula across columns, lock your range references with $ — $A:$A instead of A:A — so the formula doesn't shift as you drag it. Forgetting this is one of the most common sources of silent errors. You'll know it happened when the formula returns 0 or a wrong number in every column except the first.

Common Errors and How to Fix Them

A few errors come up repeatedly when people use SUMIF. Knowing what causes them saves you a lot of debugging time.

#VALUE! error — almost always caused by range and sum_range being different sizes. =SUMIF(A1:A10,"East",B1:B8) will throw this because A1:A10 is 10 rows but B1:B8 is only 8. Keep both ranges the same length — or use full column references like A:A and B:B to avoid the issue entirely.

Formula returns 0 unexpectedly — the criteria don't match anything. Common causes: extra spaces in your data ("East " with a trailing space won't match "East"), numbers stored as text (so ">1000" finds nothing because the "numbers" are actually text strings), or criteria text that doesn't match the case you expect — though remember, SUMIF is case-insensitive, so case itself isn't the issue.

Wrong results when copying the formula — you forgot to lock range references with $. When you drag =SUMIF(A:A,"East",B:B) sideways, A:A becomes B:B and B:B becomes C:C — probably not what you wanted. Use =SUMIF($A:$A,"East",$B:$B) to lock both ranges.

Criteria text not in quotes — writing =SUMIF(A:A,East,B:B) instead of =SUMIF(A:A,"East",B:B) causes a #NAME? error or picks up a named range called East if one exists. Always quote text criteria unless you're referencing a cell.

SUMIF vs SUMIFS: The Critical Difference

Once you know SUMIF, you'll hear about SUMIFS — and it's tempting to assume they work the same way with just an extra condition bolted on. They don't. The argument order is completely different, and getting it wrong produces silent wrong answers, not errors.

SUMIF syntax: =SUMIF(range, criteria, [sum_range]) — the range to test comes first, and sum_range comes last (and is optional).

SUMIFS syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...) — sum_range comes FIRST in SUMIFS. Then pairs of (criteria_range, criteria) follow.

This reversal catches everyone at least once. If you write =SUMIFS(A:A,B:B,">1000") when you meant to sum A based on B being over 1000 — you've actually told Excel to sum B and test A. The formula might not error out, it'll just return a wrong number.

The rule of thumb: use SUMIF for one condition, SUMIFS for two or more. And when you switch to SUMIFS, remember to move the sum_range to the front.

You can also use SUMIFS with a single condition — it handles one condition just fine — so if you find yourself constantly switching between the two, just default to SUMIFS everywhere. It's a superset. See how this compares to using sum a column in Excel with simpler approaches when conditions aren't needed.

When to Use a Pivot Table Instead

There's a point where SUMIF becomes the wrong tool — and that point is usually around 100,000 rows. Excel evaluates the entire range on every recalculation. With a large dataset and many SUMIF formulas on the sheet, recalculation can take seconds or even stall the workbook.

A pivot table in Excel aggregates data much faster because it processes the source data once and stores the aggregated result. Drag a field to Rows and a field to Values, and you've done what dozens of SUMIF formulas would do — without any formula overhead.

Power Query is the right choice when your data comes from multiple sources or needs transformation before aggregation. It runs outside the calculation engine and handles millions of rows without breaking a sweat.

The practical rule: if you're writing more than five or six SUMIF formulas on a dataset of 50k+ rows, rebuild it as a pivot table. You'll get faster recalc, easier updates, and a cleaner layout — all at once.

For smaller datasets or quick spot-checks, SUMIF remains the fastest path. Write the formula, get the answer, move on. You don't need a pivot table to answer one quick question.