How to Use SUMIF in Google Sheets: Comprehensive Guide

SUMIF adds up the values in a range that meet one condition. It answers questions like "what is the total sales for the North region?", "how much did we spend on travel?" or "what is the sum of everything above 100?" without any manual filtering.

Syntax

=SUMIF(range, criterion, [sum_range])

range: The cells to test against the criterion.
criterion: The condition a cell in range must meet.
sum_range [Optional]: The cells to add up. If omitted, SUMIF sums the cells in range themselves.

range and sum_range should be the same size and line up row by row.

Basic examples

With A1:A5 containing Apples, Bananas, Apples, Oranges, Grapes and B1:B5 containing 5, 10, 7, 3, 9:

=SUMIF(A1:A5, "Apples", B1:B5)

Returns 12 (5 + 7): the sum of column B wherever column A says Apples.

Without sum_range, the test and the sum use the same cells:

=SUMIF(B1:B5, ">5")

Returns 26 (10 + 7 + 9): the sum of all values greater than 5.

Writing criteria

Criteria are text in quotes, or a value, or a cell reference.

Exact text: "Apples" (not case-sensitive)
Exact number: 5 or "=5"
Comparison: ">100", "<=50", "<>0" (not equal to 0)
Not equal to text: "<>Apples"
Wildcards: "App*" matches any text starting with App, "?pple" matches one unknown character. To match a literal * or ?, put ~ before it ("~*").
Blank / not blank: "" (blank) and "<>" (not blank)

Use a cell in the criterion

To combine an operator with a cell, join them with &:

=SUMIF(B2:B100, ">" & E1, C2:C100)

If E1 holds 500, this sums column C wherever column B is greater than 500. Matching text from a cell needs no operator: =SUMIF(A2:A100, E1, B2:B100).

Dates

=SUMIF(A2:A100, ">=" & DATE(2026, 1, 1), B2:B100)

Sums everything dated on or after January 1, 2026. For a date range with two limits, use SUMIFS.

Practical patterns

Sum by category into a summary table

With the category names listed in E2:E6:

=SUMIF($A$2:$A$100, E2, $B$2:$B$100)

Drag it down to total each category. The $ signs lock the ranges.

Sum everything except one category

=SUMIF(A2:A100, "<>Refunds", B2:B100)

Sum text containing a word

=SUMIF(A2:A100, "*travel*", B2:B100)

Sum for several values (OR logic)

SUMIF takes one condition. For several, add the results or pass an array:

=SUMIF(A2:A100, "Apples", B2:B100) + SUMIF(A2:A100, "Oranges", B2:B100)

=SUM(SUMIF(A2:A100, {"Apples", "Oranges"}, B2:B100))

Percent of total

=SUMIF(A2:A100, E2, B2:B100) / SUM(B2:B100)

Video Example

Common problems

The sum is 0 but there are matches. The values in sum_range may be text, not numbers (left-aligned, or with a green triangle). Convert them with VALUE or re-enter them. Also check for trailing spaces in the criteria column.
Criterion on a number stored as text. SUMIF matches 5 to "5" in most cases but not reliably across mixed data. Clean the column to a single type.
Mismatched range sizes. If sum_range is a different size from range, Sheets uses the top-left cell of sum_range and resizes it to match range, which can silently shift the alignment. Keep them identical.
Wrong operator syntax. >100 without quotes is an error; write ">100".
More than one condition needed. Use SUMIFS.

SUMIF vs. SUMIFS vs. QUERY vs. pivot table

SUMIF handles a single condition. SUMIFS handles several at once. For grouped summaries across many categories, a pivot table or a QUERY with group by is quicker to build and maintain than a column of SUMIFs.

Frequently asked questions

Is SUMIF case-sensitive? No. "apples" matches "Apples".

Can SUMIF use two conditions? No. Use SUMIFS, or combine conditions with SUMPRODUCT.

Can SUMIF sum across sheets? Yes. Reference the other sheet in the ranges: =SUMIF(Sales!A:A, "North", Sales!C:C).

What if sum_range is omitted? The cells in range are summed, which only makes sense with numeric criteria like ">5".

Related Functions

SUMIFS: Sum with several conditions.
SUM: Add up a series of numbers.
COUNTIF: Count cells that meet a condition.
AVERAGEIF: Average cells that meet a condition.
QUERY: Group and total data with SQL-like syntax.

SUMIF