SUMIF is the formula that answers the most common spreadsheet question there is: “how much did X total?” — how much did we sell in January, how much did this client spend, how many hours went to that project.

If you can only learn one conditional formula in Excel, learn SUMIF. It’s simpler than SUMIFS, covers most real-world cases, and takes five minutes to understand.

What SUMIF does

SUMIF adds up the cells in one range, but only when a matching condition is true in another range.

=SUMIF(range, criteria, [sum_range])
Part What it means
range The cells you check the condition against
criteria The condition — what to match
sum_range The cells to add up (optional — if omitted, Excel sums range itself)

The classic example: a sales table with Category in column A and Amount in column B. You want the total for “Coffee” only.

=SUMIF(A2:A100, "Coffee", B2:B100)

Excel looks at each cell in A2:A100 — wherever it finds “Coffee,” it adds the matching cell from B2:B100. Everything else is ignored.

Real examples

Example 1: Sum by exact text

Sales table: column A = region, column B = sales.

=SUMIF(A2:A50, "West", B2:B50)

Returns the total sales for the West region only.

Example 2: Sum with a cell reference

Typing the criteria into the formula works, but referencing a cell is better — change the cell and the formula updates:

=SUMIF(A2:A50, D1, B2:B50)

Where D1 contains “West”. This is how you build a small dashboard: one criteria cell, a few formulas pointing at it.

Example 3: Sum values greater than a number

Criteria can be operators:

=SUMIF(B2:B50, ">100", B2:B50)

Adds up every sale over 100. Note that when the criteria is a comparison, sum_range is usually the same as range.

Example 4: Wildcards for partial matches

Use * for “any text” and ? for “any single character”:

=SUMIF(A2:A50, "Coff*", B2:B50)

Matches “Coffee”, “Coffee beans”, “Coffee cups” — anything starting with “Coff”.

SUMIF vs SUMIFS — which to use?

SUMIFS is the newer, more flexible version — it allows multiple conditions:

=SUMIFS(sum_range, range1, criteria1, range2, criteria2)

Notice the order is different — the sum range comes first in SUMIFS.

Rule of thumb: one condition → SUMIF is fine. Two or more conditions, or a sum range that’s a different shape than the criteria range → use SUMIFS. There’s no penalty for using SUMIFS for everything once you’re comfortable with it.

Common mistakes (and how to avoid them)

1. Wrong range sizes. range and sum_range must be the same size. =SUMIF(A2:A100, "Coffee", B2:B50) silently gives wrong results because the ranges are different lengths. Always match the rows.

2. Criteria text needs quotes. Text and operators go in quotes: "Coffee", ">100". Numbers and cell references don’t: 100, D1.

3. Case doesn’t matter — spelling does. SUMIF is case-insensitive (“COFFEE” matches “coffee”), but a typo matches nothing. If your result is 0, check the spelling first.

4. Leading zeros and numbers stored as text. If your data was imported, values might be text disguised as numbers. The fix: select the column → Data → Text to Columns → Finish. That converts text-numbers to real numbers SUMIF can add.

How to use SUMIF in Google Sheets

Everything above works identically in Google Sheets — the syntax is the same. The only difference worth knowing: Google Sheets has a few extra functions like SUMIFS with different edge-case behaviors, but for everyday use, copy-paste between Excel and Sheets works fine.

SUMIF with multiple conditions? Use SUMIFS

You’ll quickly hit a case where one condition isn’t enough: “total sales for the West region in January.” That’s two conditions — region and month. SUMIF can’t do it; SUMIFS can:

=SUMIFS(sum_range, range1, criteria1, range2, criteria2)

=SUMIFS(C2:C100, A2:A100, "West", B2:B100, "January")

Three things to remember with SUMIFS:

  1. The sum range comes first — the opposite of SUMIF, the #1 source of broken SUMIFS formulas.
  2. Every criteria range should be the same size as the sum range.
  3. SUMIFS also supports wildcards and operators exactly like SUMIF: =SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">100").

When to use which: one condition → SUMIF. Two or more → SUMIFS. There’s no downside to standardizing on SUMIFS once you’re comfortable with the argument order.

Combining SUMIF with IF for totals that change

A useful pattern: use IF to categorize on the fly, then SUM over the result. Say you have a list of transactions and want to flag anything above 500 as “Large”:

=SUM(IF(B2:B100 > 500, 1, 0))

Entered as an array formula (Ctrl+Shift+Enter in older Excel; Enter in Microsoft 365 and Google Sheets), this counts the number of transactions over 500. It’s a different tool than SUMIF — SUMIF adds values, this counts rows — but the two together cover most “conditional total” problems.

Debugging a SUMIF that returns wrong numbers

When SUMIF gives a result that’s obviously wrong (or zero), work through this checklist in order:

  1. Range sizes — range and sum_range must span the same rows. A mismatch silently returns a wrong total, no error message.
  2. Criteria spelling — SUMIF is case-insensitive but not typo-tolerant. Copy the exact value from a data cell rather than retyping it.
  3. Text vs numbers — imported data often stores numbers as text. SUMIF won’t match 123 against "123" stored as text. Fix: select the column → Data → Text to Columns → Finish.
  4. Hidden characters — copied values sometimes carry invisible spaces or line breaks. Use =TRIM(A2) in a helper column, or check with =LEN(A2) against a known-good cell.
  5. Wrong column order — check that range and sum_range really point at the right columns. It’s embarrassingly common to sum the wrong column.

SUMIF vs pivot tables

If you find yourself writing five SUMIFs for the same table — total by region, by month, by product — a pivot table does all of that in one shot with a drag-and-drop interface, no formulas to maintain. SUMIF’s sweet spot is a single conditional total that needs to update live as data changes, or a total embedded inside a larger formula (like a dashboard cell that references other cells). Pivot tables are better for exploratory analysis and multi-dimension summaries; SUMIF is better for formula-driven reports.

Google Sheets: same formula, same syntax

SUMIF works identically in Google Sheets — copy-paste between the two tools works for this function. Two Google-specific notes:

  • Google Sheets has a bonus: =SUMIF(range, criteria, sum_range) supports array criteria in newer versions, letting you sum across multiple match values at once.
  • The QUERY function (=QUERY(A1:C, "select A, sum(C) where B = 'January' group by A", 1)) is a heavier but far more powerful alternative if you’re doing data analysis rather than report building.

Practice file

The fastest way to learn is to build it. Make a small table: 20 rows of Item | Category | Amount, then write:

  1. Total for one category (exact match)
  2. Total over a threshold (comparison)
  3. Total with a wildcard
  4. Total with two conditions (switch to SUMIFS)

If all four return sensible numbers, you’ve got SUMIF covered for real-world use.

Two related skills round out the spreadsheet toolkit: when a lookup returns #N/A, the fixes in our VLOOKUP troubleshooting guide apply to SUMIF’s sibling functions too — and if you’re still deciding between Excel and Google Sheets for this kind of work, our Excel vs Google Sheets comparison covers the practical differences.

FAQ

Can SUMIF look at two columns at once? Not directly — that’s what SUMIFS is for. Use SUMIFS when you need multiple conditions.

Why is my SUMIF returning 0? Most common causes: criteria misspelled, range sizes mismatched, or numbers stored as text. Work through those three in order.

Does SUMIF work on other sheets in the workbook? Yes — reference them normally: =SUMIF(Sheet2!A2:A50, "Coffee", Sheet2!B2:B50).

Last updated: August 24, 2026.