SUMIF in Google Sheets

Adds the cells that meet one condition. The condition can be checked in the same range or in another column.

In Google Sheets

SUMIF takes the same arguments in Google Sheets, and criteria such as ">100" and "N*" work the same way.

How to use SUMIF

SUMIF is a total with a filter. Picture a pile of receipts where you only want to add the ones from one shop. SUMIF looks at each row, checks the condition, and counts the amount only when the condition is met.

You give it three things: the range to check (the shop names), the condition ("North"), and the range that holds the amounts to add. In =SUMIF(A2:A10,"North",C2:C10) the two ranges line up row by row. If you leave out the last argument, SUMIF adds the cells it checked, as in =SUMIF(C2:C10,">300").

The condition can be text, a number, or a comparison inside quotes such as ">300" or "<>North". Wildcards let you match part of a text: "*shoe*" matches every cell with shoe somewhere in it. To compare with a value in a cell, join the two with &, like ">"&E1.

SUMIF holds one condition only. When region and product must both match, use SUMIFS, which puts the sum range first. To count the matching rows instead of adding them, use COUNTIF.

Syntax

SUMIF(range, criteria, [sum_range])
range
The cells the condition is checked against.
criteria
The condition, such as "North", ">300" or a cell like E1.
sum_range (optional)
The cells to add. If left out, range itself is added.

Examples

E1=SUMIF(A2:A6,"North",C2:C6)
ABCDE
1RegionProductSales1170
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

In plain words: Add up the sales in C2:C6 on the rows where the region in A2:A6 is North.

E1=SUMIF(C2:C6,">300")
ABCDE
1RegionProductSales920
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

Common SUMIF mistakes

  • SUMIF returns 0 although matching rows exist

    Usually the cells do not match exactly. Numbers stored as text in the sum range are skipped, and a category like North with a space at the end is not the same as North. Turn the text into real numbers, clean the names with TRIM, or use a wildcard such as "North*".

  • A condition like ">E1" adds nothing

    Inside quotes, E1 is just the two letters E1. Join the operator and the cell with &: =SUMIF(C2:C6,">"&E1).

  • SUMIF rejects a condition such as >300 written without quotes

    A condition with an operator is text, so it needs quotes: =SUMIF(C2:C6,">300").

  • SUMIF adds the wrong rows

    SUMIF lines the sum range up with the range from its first cell. In =SUMIF(A2:A6,"North",C3:C7) A2 is paired with C3, so every amount is read one row too low. Start both ranges in the same row.

Practice SUMIF

Related functions