How to Use SUMIF in Excel
SUMIF adds up values in a range that meet a single condition. Here's the syntax, practical examples, and common mistakes — including when to upgrade to SUMIFS.
SUMIF adds up all values in a range where a corresponding cell meets one condition. It's the go-to formula for "sum all sales from the West region" or "total all invoices over $500" — any time you need a conditional total from a single column.
The syntax
=SUMIF(range, criteria, [sum_range])- range — the column you're checking the condition against
- criteria — the condition (a value, text, cell reference, or comparison like
">500") - [sum_range] — the column to add up. If omitted, Excel sums the
rangecolumn itself
Step-by-step example
You have sales data: Column A = Region, Column B = Revenue. You want total revenue from the "West" region.
Step 1. Click the cell where you want the total.
Step 2. Write the formula:
=SUMIF(A2:A100, "West", B2:B100)Step 3. To make it dynamic — pulling the region from a cell instead of hardcoding it:
=SUMIF(A2:A100, E2, B2:B100)Now change E2 to any region and the total updates automatically.
✦ TIP
Use whole column references like A:A instead of A2:A100 so the formula automatically includes new rows as data grows.
Using comparison operators
Criteria can include operators wrapped in quotes:
=SUMIF(B2:B100, ">500", C2:C100)| Criteria example | What it matches |
|---|---|
"West" | Exact text match |
">500" | Numbers greater than 500 |
"<>" | Non-blank cells |
"*Corp*" | Text containing "Corp" |
">"&D2 | Greater than the value in D2 |
SUMIF vs SUMIFS
SUMIF handles one condition. The moment you need two — West region and revenue over $500 — use SUMIFS:
=SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">500")SUMIFS also puts the sum range first, unlike SUMIF. It's a common source of confusion when switching between the two.
The Griddy way
SUMIF is straightforward until you need partial text matches, date conditions, or OR logic — at which point the formula gets messy fast. Just describe what you need:
"Sum all revenue where the customer name contains 'Corp' and the deal closed after January 1st"
Griddy writes the right formula for your exact column names and data structure.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.