How to Use SUMIFS and COUNTIFS in Google Sheets (Multiple Criteria)

By Gerard Fernandez · Updated · 3 min read

SUMIF and COUNTIF handle one condition. When you need two or more, for example "Food expenses paid by card" or "orders over 50 in March", use their plural versions: SUMIFS and COUNTIFS.

The syntax

=SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2, …)
=COUNTIFS(criteria_range1, criterion1, criteria_range2, criterion2, …)

Note the order: in SUMIFS the range to add comes first, unlike SUMIF where it comes last. All ranges must be the same size.

Example: expenses by category and payment method

The table lists expenses with a Category (column A), a payment Method (column B) and an Amount (column C). To add up Food expenses paid by card:

=SUMIFS(C2:C9, A2:A9, "Food", B2:B9, "Card")

And to count how many there were:

=COUNTIFS(A2:A9, "Food", B2:B9, "Card")
Google Sheets expense list with Category, Method, Amount and Date; SUMIFS returns 46.5 for Food paid by Card and COUNTIFS returns 3
Real test: only rows 2, 5 and 7 are Food and Card, so the total is 12 + 25 + 9.5 = 46.5 and the count is 3.

Criteria you can use

You want…Criterion
Exactly equal to a text"Food"
Not equal to"<>Food"
Greater than a number">50"
Greater than a value in a cell">"&F1
Contains a word"*coffee*"
Not empty"<>"
Use the value in another cellF2 (no quotes)

We tried these on the same 8 rows: "<>Food" counted 3 rows, ">20" counted 3 amounts, "*oo*" matched the 5 Food rows, and "<>" counted all 8.

Between two dates

Use the same date column twice, once for the start and once for the end. With dates in column D and the start and end dates in F1 and F2:

=SUMIFS(C2:C9, D2:D9, ">="&F1, D2:D9, "<="&F2)

In our test, from March 5 to March 15 (both included) it returned 123: the four rows dated 3/5, 3/9, 3/12 and 3/15. You can also write the dates inside the formula with DATE: ">="&DATE(2026,3,5).

"Or" conditions

All conditions in SUMIFS must be true at the same time ("and"). For "Food or Transport", add two SUMIFS together:

=SUMIFS(C2:C9, A2:A9, "Food") + SUMIFS(C2:C9, A2:A9, "Transport")

That gave 103.5 in our test: 69.5 for Food plus 34 for Transport.

SUMIF: one condition

SUM adds up everything in a range. SUMIF adds up only the values that meet a condition, for example "total spent on Food" or "sales above 1,000". It's one of the most useful functions for budgets, expense trackers and sales reports.

SUMIF syntax

=SUMIF(range, criterion, [sum_range])
  • range – the cells to test against the condition.
  • criterion – the condition, such as "Food", ">100" or a cell reference.
  • sum_range – optional. The cells to add up. If you leave it out, Sheets adds up the cells in range itself.

Example: total per category

An expense list has the category in column A, the item in column B and the amount in column C. To add up everything in the Food category, with the category name typed in E2:

=SUMIF(A2:A7, E2, C2:C7)

The result is 97.50: 54.20 (groceries) + 38.50 (restaurant) + 4.80 (coffee).

Google Sheets expense list with Category, Item, Amount and Date; =SUMIF(A2:A7, E2, C2:C7) in F2 returns 97.50 for Food
Real test: the Food total in F2. Every formula on this page was checked against this same list.

Tip: using a cell like E2 for the condition, instead of typing "Food" into the formula, lets you build a small summary table: list every category in column E and copy the formula down.

Conditions with numbers

Put the comparison operator and the number together inside quotes:

FormulaAdds up
=SUMIF(C2:C7, ">50")Amounts greater than 50 (126.30 in our test)
=SUMIF(C2:C7, "<=20")Amounts of 20 or less (4.80)
=SUMIF(A2:A7, "<>Food", C2:C7)Everything except Food (147.10)

To compare with a number stored in a cell, join the operator and the cell with &:

=SUMIF(C2:C7, ">"&H1)

Partial text matches with wildcards

An asterisk * stands for any number of characters, and a question mark ? for exactly one character:

=SUMIF(B2:B7, "*pass*", C2:C7)

This adds every amount whose item contains "pass", such as "Bus pass" or "Train pass": 75.00 in our test. Matching ignores upper and lower case; "*PASS*" gave the same 75.00.

Conditions with dates

If column D holds dates, add up everything from 2026 onwards with:

=SUMIFS(C2:C100, D2:D100, ">="&DATE(2026,1,1))

Using DATE() avoids problems with different date formats in different countries. In our test list, where the groceries were bought on December 28, 2025, this returned 190.40: everything except that row.

COUNTIF: counting with one condition

COUNTIF counts how many cells in a range meet a condition: how many orders are "Paid", how many scores are above 80, how many times a name appears. It's quick to write and works for text, numbers and dates.

COUNTIF syntax

=COUNTIF(range, criterion)
  • range – the cells to check.
  • criterion – the condition, for example "Food", ">80" or a cell reference.

Count cells with specific text

In an expense list with categories in column A, count how many purchases were Food:

=COUNTIF(A2:A7, "Food")
Google Sheets expense list; =COUNTIF(A2:A7, E2) in F5 returns 3 Food purchases
Real test: three rows are Food. Here the condition is in E2, so the formula is =COUNTIF(A2:A7, E2).

COUNTIF ignores upper and lower case: in our test, "food" also returned 3.

8 common COUNTIF examples

You want to count…Formula
Cells equal to a value in another cell=COUNTIF(A2:A100, E2)
Numbers greater than 80=COUNTIF(B2:B100, ">80")
Numbers greater than the value in H1=COUNTIF(B2:B100, ">"&H1)
Cells that are not "Done"=COUNTIF(C2:C100, "<>Done")
Cells that contain "apple" anywhere=COUNTIF(A2:A100, "*apple*")
Cells that start with "INV-"=COUNTIF(A2:A100, "INV-*")
Empty cells=COUNTBLANK(A2:A100)
Non-empty cells=COUNTA(A2:A100)

Watch out: "<>Done" also counts empty cells. In our test, =COUNTIF(A2:A10, "<>Food") on a list with 3 non-Food rows and 3 empty rows returned 6, not 3. If your range includes empty rows at the bottom, limit the range to the rows with data, or use COUNTIFS with a second condition "<>" (not empty).

Count checkboxes that are ticked

A ticked checkbox has the value TRUE, so:

=COUNTIF(C2:C20, TRUE)

See how to add checkboxes in Google Sheets for a full to-do list example.

Count cells with text, numbers or blanks

COUNTIF is one of several counting functions, and each one counts something slightly different:

FormulaCounts
=COUNTA(A2:A100)Every cell that isn't empty: text, numbers, dates
=COUNT(A2:A100)Only numbers (and dates)
=COUNTIF(A2:A100, "*")Only text
=COUNTBLANK(A2:A100)Only empty cells
=COUNTUNIQUE(A2:A100)Each different value once

We checked these on our expense list (6 rows, 4 columns, 3 empty rows below): COUNTA gave 6, COUNTBLANK 3, COUNTUNIQUE on the categories 3, and on A2:D7 both COUNT (numbers and dates) and COUNTIF(…, "*") (text) gave 12. The asterisk * is a wildcard for "any text". To count cells that contain a word anywhere, put it on both sides: =COUNTIF(A2:A100, "*invoice*"). COUNTIF isn't case-sensitive.

Frequently asked questions

Why does SUMIFS return 0?

Usually a spelling or spacing difference, or numbers stored as text in the sum range. In our test, searching for "Food " (with a space) returned 0. Check with a COUNTIFS using the same criteria: if it also returns 0, no row matches.

Ranges of different sizes don't give 0: they give #VALUE!. In our test, =SUMIFS(C2:C9, A2:A8, "Food") showed "Array arguments to SUMIFS are of different size."

Is SUMIFS case-sensitive?

No. In our test, "food" and "card" in lower case returned the same 46.5.

What about a single condition?

SUMIFS works fine with one condition, but see SUMIF and COUNTIF for the simpler versions.