How to Use SUMIF in Google Sheets

Add up only the cells that match a condition with SUMIF in Google Sheets, using text, number comparisons, or wildcards, and SUMIFS for several conditions.

You have expenses with a category in column A and an amount in column B, and you want the total spent on Rent. SUMIF adds up the amounts only for rows that match.

Type the basic formula

=SUMIF(A2:A100, "Rent", B2:B100)
  • range is the column to test, here the categories.
  • criterion is what a cell has to match. Text goes in double quotes and is not case sensitive.
  • sum_range is the column of numbers to add for the matching rows.

With three Rent rows of 1200 each, the cell shows 3600.

Reference a cell for the criterion

=SUMIF(A2:A100, D1, B2:B100)

With Rent typed in D1, the result is the same as before. Change D1 to Groceries and the total updates without editing the formula.

Sum numbers above or below a threshold

=SUMIF(B2:B100, ">100")

When the column being tested is also the column being summed, leave out the third argument. The operator and the number sit together inside the quotes, and "<=50" and "<>0" work the same way. To compare against a cell, join the operator to the reference: =SUMIF(B2:B100, ">"&D2).

Match partial text with wildcards

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

* stands for any run of characters and ? for exactly one, so this adds Rent, Rental car, and Rent deposit. To match a literal asterisk or question mark, put a tilde before it, as in "~*".

Use SUMIFS for more than one condition

=SUMIFS(C2:C100, A2:A100, "Rent", B2:B100, ">=2026-01-01")
  • The sum range comes first in SUMIFS.
  • Each pair after it is a range to test and its criterion.

A row is added only when every pair matches, so this totals Rent amounts in column C dated on or after January 1, 2026 in column B. Add more pairs for more conditions.

About range sizes. The sum range and the criteria range must cover the same number of rows, starting from the same row. Testing A2:A100 against B2:B99 gives wrong totals with no error message.

More Google Sheets how-tos