SUMIF Function in Google Sheets
Adds the values in a range that meet a single condition. The conditional version of SUM, used for category totals, filtered sums, and rolling running balances.
Syntax
SUMIF(range, criterion, [sum_range])Returns: The total of values from sum_range (or range) where the corresponding row in range matches criterion.
Excel equivalent: SUMIF (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| range | Required | The range to test against criterion. |
| criterion | Required | The condition to match. Can be a value (10), a text string ("Apple"), or a comparison (">100", "<>0"). |
| sum_range | Optional | The range to sum when criterion is met. If omitted, range itself is summed. |
Examples
Sum sales for a single product
=SUMIF(A2:A100, "Apple", B2:B100)Looks at column A for rows where the product is "Apple", and sums the matching values in column B.
Sum values above a threshold
=SUMIF(B2:B100, ">1000")When sum_range is omitted, SUMIF sums the range itself. Total of all values in B that are greater than 1000.
Wildcard match for partial text
=SUMIF(A2:A100, "*Pro*", B2:B100)Sums where column A contains "Pro" anywhere in the text. * is a wildcard for any characters, ? is a wildcard for a single character.
When to use an alternative
- SUMIFS — You have two or more conditions.
- QUERY — You want SUM with GROUP BY across multiple categories at once.
- SUMPRODUCT — You need a more complex condition (e.g., date math) that criterion strings can't express.
Common errors and how to fix them
Returns 0 unexpectedly
Cause: criterion is a number but the range stores numbers as text (or vice versa).
Fix: Ensure consistent types. Use VALUE() to normalize, or quote the number: SUMIF(A:A, "5", B:B) treats 5 as text-equivalent.
Wrong total
Cause: range and sum_range are different sizes — SUMIF uses the first cell of sum_range and extrapolates the same shape as range.
Fix: Always make range and sum_range the same size and shape.
Related functions
Frequently Asked Questions
How do I use SUMIF with multiple conditions?
You can't with SUMIF — it only handles one condition. Use SUMIFS for multiple conditions, or SUMPRODUCT for very complex matching logic.
Can SUMIF use a date range?
Yes, with criterion strings: SUMIF(A:A, ">="&DATE(2026,1,1), B:B) sums where column A is on or after Jan 1 2026. The & concatenates the comparison operator with the date value.