What SUMIF Does
SUMIF adds up values in a range that meet a specific condition. For example, you can sum all sales from a particular region or total expenses above a certain amount. It saves you from manually filtering data before summing.
Syntax
=SUMIF(range, criterion, [sum_range])
| Parameter | Description |
|---|---|
| range | The range of cells to evaluate against the criterion. |
| criterion | The condition that determines which cells to sum. Can be a number, text, expression, or cell reference. |
| sum_range | The range of cells to actually sum. If omitted, the cells in range are summed. |
Basic Examples
Example 1: Sum Sales by Region
| A | B | |
|---|---|---|
| 1 | Region | Sales |
| 2 | East | 4500 |
| 3 | West | 3200 |
| 4 | East | 6100 |
| 5 | West | 2800 |
=SUMIF(A2:A5, "East", B2:B5)
Result: 10600 (4500 + 6100)
Example 2: Sum Values Greater Than a Threshold
=SUMIF(B2:B5, ">3000", B2:B5)
Result: 13800 (4500 + 6100 + 3200)
Note: When using comparison operators, wrap the criterion in quotes.
Example 3: Sum by Cell Reference
If cell D1 contains the region you want to total:
=SUMIF(A2:A5, D1, B2:B5)
This makes your formula dynamic — change D1 and the total updates automatically.
Advanced Examples
Wildcard Matching
Sum revenue for all products that start with "Pro":
=SUMIF(A2:A100, "Pro*", B2:B100)
This matches "Pro Plan", "Professional", "ProMax", etc. Use ? for a single-character wildcard and * for any number of characters.
Summing by Date Range
Sum all expenses on or after January 1, 2026:
=SUMIF(A2:A100, ">="&DATE(2026,1,1), B2:B100)
The ampersand (&) joins the operator ">=" with the DATE function to build the criterion dynamically.
Common Mistakes
- Forgetting quotes around operators. Criteria like
>100must be written as">100". Without quotes, Google Sheets throws a parsing error. - Mismatched range sizes. The
rangeandsum_rangeshould have the same number of rows (or columns). If they differ, SUMIF only uses the dimensions ofrangeto decide which cells insum_rangeto add. - Using SUMIF for multiple conditions. SUMIF handles only one criterion. For two or more conditions (e.g., region = "East" AND month = "Jan"), use SUMIFS instead.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "sum all sales where region is East" — and the formula is generated for you. Install SheetAI to try it free.