SUMIF Function in Excel
SUMIF adds up numbers that meet a single condition. It's Excel's most-used conditional aggregation function — the foundation of every simple report, every category subtotal, every "how much did we sell of X" question. Here's how to use it well.
What it does: Sums the values in sum_range where the corresponding cells in range meet criteria. If sum_range is omitted, Excel sums range itself.
What SUMIF does
SUMIF answers questions of the form "how much of X do I have?" It looks through a range for cells matching your criteria, and adds up the corresponding numeric values in a parallel range. Classic use: sum sales by region, sum expenses by category, sum hours by employee.
The mental model: SUMIF walks two ranges in lockstep. For each row where range's value matches criteria, it adds the corresponding value from sum_range. When both ranges are the same, SUMIF sums the values that meet the condition directly.
SUMIFS from the start — its argument order is different (sum range first!), so switching later means rewriting formulas. Most experienced Excel users default to SUMIFS even for single conditions.
Syntax breakdown
range Required
The range of cells that Excel checks against your criteria. This is where the condition is evaluated — not what gets summed (unless you omit sum_range). Can be a single column, single row, or a rectangular range.
criteria Required
The condition that range values must match. Can be a number (100), a text string in quotes ("East"), a comparison (">500"), a cell reference (B2), or a wildcard pattern ("North*"). Text and comparison operators must always be wrapped in double quotes.
sum_range Optional
The range whose values are actually summed. Must be the same size and shape as range. If omitted, SUMIF sums range itself — useful when you're summing numbers based on their own values (e.g., "sum all values greater than 100").
5 real-world examples
Example 1: Sum sales by region
Column A has region names, column B has sales amounts. You want total sales for the East region:
=SUMIF(A2:A100, "East", B2:B100)Result: $47,250 — the sum of every value in B2:B100 whose corresponding row in A2:A100 equals "East".
Example 2: Sum values greater than a threshold
You want the total of all sales above $500 (with no separate criteria range — just the values themselves):
=SUMIF(B2:B100, ">500")Result: $32,180. Because sum_range is omitted, SUMIF checks and sums the same range. Note the comparison operator inside quotes.
Example 3: Sum with a wildcard match
Column A has product names. You want to sum sales of every product that starts with "Blue":
=SUMIF(A2:A100, "Blue*", B2:B100)Result: $18,900 — includes "Blueberry", "Blue Jeans", "Bluebird", etc. Use * for any characters, ? for a single character.
Example 4: Sum using a cell reference for criteria
You want the total to update when you change a lookup cell. Type the region name in E2:
Result: Depends on E2. Change E2 from "East" to "West" and the total updates automatically. This is how most dashboard totals are wired.
Example 5: Sum for a date range (using two SUMIFs)
You want sales in Q1 — dates between Jan 1 and Mar 31. Since SUMIF handles only one condition, subtract:
=SUMIF(A2:A100, "<="&DATE(2026,3,31), B2:B100) - SUMIF(A2:A100, "<"&DATE(2026,1,1), B2:B100)Result: Total sales in Q1 2026. Note the & operator to concatenate the comparison with the date. In practice, SUMIFS handles this in one call — see the primer above.
Common errors and how to fix them
Usually means sum_range is a different size than range, or one of the ranges references cells with incompatible data types. Fix: check that both ranges cover the same number of rows and columns.
Most common cause: criteria doesn't match because of trailing spaces, hidden characters, or case-sensitivity in numbers stored as text. Use TRIM on the source data or check with =A2="East" to see if the equality actually holds.
Usually a range-misalignment bug: range starts at row 2 but sum_range starts at row 3, so values are matched to the wrong rows. Fix: verify both ranges start and end at the same row numbers.
Wildcards only work when criteria are wrapped in quotes: "North*" works, North* doesn't. Also: to match a literal asterisk or question mark, precede it with tilde — "~*" matches an actual asterisk character.
SUMIF vs SUMIFS vs SUMPRODUCT
| Function | Best for | Watch out for |
|---|---|---|
| SUMIF | One condition, simple aggregation | Only single condition; argument order is criteria-first |
| SUMIFS | Multiple conditions AND'd together | Argument order is sum-range-first (opposite of SUMIF) |
| SUMPRODUCT | Complex logic including OR conditions or array math | Slower on large ranges; syntax is denser |
| FILTER + SUM | Modern Excel; combines with dynamic arrays | Requires Excel 365 / 2021+; different mental model |
Version compatibility
Download the practice workbook
Every example above, plus 15 more SUMIF patterns you can adapt for your own reports.
Related functions
Frequently asked questions
What's the difference between SUMIF and SUMIFS argument order?
SUMIF takes (range, criteria, [sum_range]) — the criteria range comes first. SUMIFS takes (sum_range, criteria_range1, criteria1, ...) — the sum range comes first. This is one of the most common mistakes when converting a SUMIF to SUMIFS. Read carefully when switching.
Can SUMIF handle multiple criteria?
Not directly — that's what SUMIFS is for. You can simulate OR conditions by adding two SUMIFs together: =SUMIF(A:A, "East", B:B) + SUMIF(A:A, "West", B:B). But for AND logic across different columns, use SUMIFS instead.
Does SUMIF work with dates?
Yes — use comparison operators inside quotes and concatenate the date with &: =SUMIF(A:A, ">="&DATE(2026,1,1), B:B). Or reference a cell containing the date: =SUMIF(A:A, ">="&E2, B:B). Direct comparison like =SUMIF(A:A, ">=1/1/2026", B:B) often works too but is less reliable across locales.
How do I sum where a cell is NOT equal to a value?
Use the <> operator: =SUMIF(A:A, "<>East", B:B) sums all rows where column A is anything but "East". To exclude blanks as well: =SUMIF(A:A, "<>East", B:B) - SUMIF(A:A, "", B:B).
Can I use SUMIF across multiple sheets?
Not in one call — SUMIF doesn't support 3D references across sheets. Workaround: add multiple SUMIFs together: =SUMIF(Sheet1!A:A, "East", Sheet1!B:B) + SUMIF(Sheet2!A:A, "East", Sheet2!B:B). For many sheets, use a helper column that consolidates data first, or Power Query.
Why does SUMIF return 0 even though matching rows exist?
Three usual suspects: (1) the criteria has hidden trailing spaces — try =SUMIF(TRIM(A:A), "East", B:B) as an array formula, or clean the source data; (2) numbers are stored as text in sum_range — convert them with VALUE or multiply by 1; (3) case-sensitive text isn't matching — SUMIF is case-insensitive, but if you meant case-sensitive matching, you need SUMPRODUCT with EXACT.
Is SUMIF faster than SUMPRODUCT?
Yes — SUMIF is optimized natively while SUMPRODUCT does full array math. On ranges under a few thousand rows the difference is invisible. On very large ranges (100,000+ rows) SUMIF is significantly faster. For truly performance-critical workbooks, consider Excel Tables + SUMIFS or Power Pivot.
Build conditional totals faster — with the Sheets & Cells AI Add-in
Describe what you want to sum in plain English — "total sales in East region for Q1" — and our Excel Add-in writes the SUMIF or SUMIFS for you, ready to paste. All inside Excel.
Learn about the Add-in →