How to Use SUMIFS and COUNTIFS in Google Sheets (Multiple Criteria)
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")
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 cell | F2 (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).
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:
| Formula | Adds 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")
=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:
| Formula | Counts |
|---|---|
=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.