Conditional formulas are the backbone of any serious Google Sheets workflow. If you've ever needed to sum revenue for a specific product, count how many deals closed last quarter, or tally up entries that match certain criteria, you've needed SUMIF or COUNTIF.
This article covers everything from basic syntax to advanced multi-criteria techniques, with real formula examples you can copy straight into your spreadsheets.
We'll use this sample dataset throughout — a sales tracking spreadsheet:
| A (Product) | B (Region) | C (Salesperson) | D (Date) | E (Amount) | F (Status) | |
|---|---|---|---|---|---|---|
| 2 | Widget | North | Alice | 2026-01-15 | 1200 | Closed |
| 3 | Gadget | South | Bob | 2026-01-22 | 800 | Pending |
| 4 | Widget | East | Alice | 2026-02-03 | 1500 | Closed |
| 5 | Gizmo | North | Charlie | 2026-02-14 | 2000 | Closed |
| 6 | Gadget | West | Bob | 2026-03-01 | 950 | Cancelled |
| 7 | Widget | South | Charlie | 2026-03-10 | 1100 | Pending |
| 8 | Gizmo | East | Alice | 2026-03-18 | 1750 | Closed |
What Are SUMIF and COUNTIF?
SUMIF adds up values in a range where corresponding cells meet a condition. COUNTIF counts how many cells in a range meet a condition. Both belong to Google Sheets' family of conditional functions — formulas that only operate on data matching criteria you define.
Use SUMIF when you need a total. "What's the total revenue from Widget sales?" Use COUNTIF when you need a count. "How many deals are still Pending?"
Here's the basic syntax for each:
=SUMIF(range, criterion, [sum_range])
=COUNTIF(range, criterion)The key difference: SUMIF takes an optional third argument — the range of values to sum. COUNTIF doesn't need one because it's just counting matches, not summing separate values.
Both functions are case-insensitive by default. Searching for "widget" matches "Widget", "WIDGET", and "widget" alike.
SUMIF in Google Sheets
Basic SUMIF Syntax and Examples
The SUMIF function takes three arguments:
=SUMIF(range, criterion, [sum_range])- range — the cells to evaluate against the criterion
- criterion — the condition that must be met (text, number, or expression)
- sum_range — (optional) the cells to sum. If omitted, Google Sheets sums the
rangeitself
Sum all sales for "Widget":
=SUMIF(A2:A8, "Widget", E2:E8)This returns 3800 (1200 + 1500 + 1100). The formula checks each cell in A2:A8 for the text "Widget", then sums the corresponding cells in E2:E8.
Sum sales greater than 1000:
=SUMIF(E2:E8, ">1000", E2:E8)Returns 7550 (1200 + 1500 + 2000 + 1100 + 1750). Notice the comparison operator is wrapped in quotes along with the number.
Sum sales less than or equal to 1000:
=SUMIF(E2:E8, "<=1000", E2:E8)Returns 1750 (800 + 950).
When the range you're evaluating is the range you want to sum, you can omit the third argument:
=SUMIF(E2:E8, ">1000")This produces the same result — Google Sheets sums E2:E8 for rows where E2:E8 exceeds 1000.
SUMIF with Dates
Date criteria in SUMIF require a bit of care. You can use the DATE() function, TODAY(), or date strings.
Sum sales after February 1, 2026:
=SUMIF(D2:D8, ">"&DATE(2026,2,1), E2:E8)Returns 7300 (1500 + 2000 + 950 + 1100 + 1750). The & operator concatenates the ">" comparison with the date value.
Sum sales from today or earlier:
=SUMIF(D2:D8, "<="&TODAY(), E2:E8)This dynamically adjusts. Every day, the result updates to include only sales on or before the current date.
Sum sales in a specific month:
There's no single SUMIF call for "all of February." Use SUMIFS with a date range instead:
=SUMIFS(E2:E8, D2:D8, ">="&DATE(2026,2,1), D2:D8, "<"&DATE(2026,3,1))Returns 3500 (1500 + 2000) — all sales where the date falls within February 2026.
SUMIF with Wildcards
Google Sheets supports two wildcards in SUMIF criteria:
*— matches any sequence of characters (including none)?— matches exactly one character
Sum sales for products starting with "G":
=SUMIF(A2:A8, "G*", E2:E8)Returns 5500 (800 + 2000 + 950 + 1750). Matches both "Gadget" and "Gizmo".
Sum sales for products containing "dg":
=SUMIF(A2:A8, "*dg*", E2:E8)Returns 1750 (800 + 950). Matches "Gadget" because it contains "dg".
If you need to match a literal asterisk or question mark, prefix it with a tilde: ~* or ~?.
COUNTIF in Google Sheets
Basic COUNTIF Syntax and Examples
The COUNTIF function takes just two arguments:
=COUNTIF(range, criterion)- range — the cells to evaluate
- criterion — the condition to match
Count how many sales are "Widget":
=COUNTIF(A2:A8, "Widget")Returns 3.
Count sales over 1000:
=COUNTIF(E2:E8, ">1000")Returns 5.
Count deals in the "North" region:
=COUNTIF(B2:B8, "North")Returns 2.
Wildcards work identically to SUMIF. Count products starting with "G":
=COUNTIF(A2:A8, "G*")Returns 4 (two Gadget rows, two Gizmo rows).
COUNTIF Not Blank (and Blank)
Counting non-empty or empty cells is one of the most common COUNTIF use cases.
Count non-blank cells:
=COUNTIF(F2:F8, "<>")The <> operator means "not equal to nothing" — in other words, not blank. This returns 7 for our dataset since every Status cell has a value.
Count blank cells:
=COUNTIF(F2:F8, "")Returns 0 in our dataset. If any Status cell were empty, this would count it.
Alternative: COUNTA and COUNTBLANK
Google Sheets also provides dedicated functions for this:
=COUNTA(F2:F8) // Count non-blank cells — returns 7
=COUNTBLANK(F2:F8) // Count blank cells — returns 0COUNTA is simpler when you just need a non-blank count. But COUNTIF with <> is more flexible because you can combine it with other techniques:
=COUNTIF(F2:F8, "<>"&"Cancelled")This counts cells that are not equal to "Cancelled" — returns 6.
SUMIFS and COUNTIFS: Multiple Criteria
The single-criterion versions are powerful, but real-world data analysis usually demands multiple conditions. That's where SUMIFS and COUNTIFS come in.
SUMIFS Syntax and Examples
SUMIFS extends SUMIF to support multiple criteria. Note the different argument order — the sum range comes first:
=SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2, ...)All criteria must be met (AND logic) for a row to be included.
Sum Widget sales in the North region:
=SUMIFS(E2:E8, A2:A8, "Widget", B2:B8, "North")Returns 1200. Only row 2 matches both conditions.
Sum closed deals over 1000:
=SUMIFS(E2:E8, F2:F8, "Closed", E2:E8, ">1000")Returns 6450 (1200 + 1500 + 2000 + 1750). Each row must have Status = "Closed" AND Amount > 1000.
Sum Alice's sales in Q1 2026:
=SUMIFS(E2:E8, C2:C8, "Alice", D2:D8, ">="&DATE(2026,1,1), D2:D8, "<"&DATE(2026,4,1))Returns 4450 (1200 + 1500 + 1750). Three criteria applied: salesperson, start date, and end date.
COUNTIFS Syntax and Examples
COUNTIFS follows the same pattern as COUNTIF, but with multiple range/criterion pairs:
=COUNTIFS(criteria_range1, criterion1, criteria_range2, criterion2, ...)Count closed Widget deals:
=COUNTIFS(A2:A8, "Widget", F2:F8, "Closed")Returns 2 (rows 2 and 4).
Count sales by Alice over 1500:
=COUNTIFS(C2:C8, "Alice", E2:E8, ">1500")Returns 1 (only row 8, Amount = 1750).
OR Logic with Multiple Criteria
SUMIFS and COUNTIFS use AND logic — every condition must be true. For OR logic (any condition can be true), you have two approaches.
Approach 1: Add multiple SUMIF or COUNTIF calls
Sum sales for Widget OR Gizmo:
=SUMIF(A2:A8, "Widget", E2:E8) + SUMIF(A2:A8, "Gizmo", E2:E8)Returns 7550 (3800 + 3750). Simple and readable, but gets unwieldy with many OR conditions.
Approach 2: SUMPRODUCT for complex OR logic
Sum sales where the product is Widget OR the region is South:
=SUMPRODUCT(((A2:A8="Widget")+(B2:B8="South")>0)*E2:E8)The >0 converts the addition result to a simple TRUE/FALSE, preventing double-counting when both conditions are true for the same row.
For OR logic with counting:
=SUMPRODUCT(((A2:A8="Widget")+(F2:F8="Pending"))>0)Returns 4. Counts rows where the product is Widget OR the status is Pending.
Advanced Techniques
Combining with ARRAYFORMULA
SUMIF and COUNTIF don't always play nicely with ARRAYFORMULA, but there are useful patterns.
Running conditional count across a list of products:
Suppose cells H2:H4 contain the unique product names: Widget, Gadget, Gizmo. You want the sales count for each:
=ARRAYFORMULA(COUNTIF(A2:A8, H2:H4))Place this in I2, and it spills results into I2:I4 — returning 3, 2, 2 respectively.
Conditional sum across categories:
=ARRAYFORMULA(SUMIF(A2:A8, H2:H4, E2:E8))Returns 3800, 1750, 3750 in I2:I4. This pattern is extremely useful for building summary tables without writing a separate formula for each row.
Case-Sensitive Conditional Counting and Summing
By default, SUMIF and COUNTIF are case-insensitive. For case sensitivity, combine SUMPRODUCT with EXACT:
Case-sensitive count:
=SUMPRODUCT(EXACT(A2:A8, "Widget")*1)Returns 3 — matching only "Widget" with that exact capitalization.
Case-sensitive sum:
=SUMPRODUCT(EXACT(A2:A8, "Widget")*E2:E8)Returns 3800. The EXACT function returns TRUE/FALSE for each cell, then multiplication converts TRUE to 1 and FALSE to 0.
Using Cell References in Criteria
Hard-coding criteria works for one-off formulas, but cell references make your spreadsheets dynamic and maintainable.
Reference a cell directly:
=SUMIF(A2:A8, H1, E2:E8)If H1 contains "Widget", this sums Widget sales. Change H1 to "Gizmo" and the formula updates automatically.
Build criteria with concatenation:
=SUMIF(E2:E8, ">"&H2, E2:E8)If H2 contains 1000, this becomes >1000. The & operator joins the comparison operator with the cell value.
Dynamic date thresholds:
=COUNTIFS(D2:D8, ">="&H3, D2:D8, "<="&H4)If H3 has a start date and H4 has an end date, this counts rows within that range. Perfect for dashboard filters.
Supercharge Your Google Sheets with AI
Unlimited Sheets lets you use AI directly in your spreadsheet — generate formulas, extract data, and automate tasks without leaving Google Sheets.
Install Free Add-on →Common Errors and How to Fix Them
Conditional formulas are straightforward once you understand the patterns, but a few mistakes trip people up consistently.
Range size mismatch in SUMIFS/COUNTIFS
Every criteria range must be the same size as the sum range (for SUMIFS) or the same size as each other (for COUNTIFS). This formula breaks:
// ERROR: A2:A8 has 7 rows, but E2:E10 has 9 rows
=SUMIFS(E2:E10, A2:A8, "Widget")Google Sheets throws a #VALUE! error. Fix it by making all ranges the same size:
=SUMIFS(E2:E8, A2:A8, "Widget")Wrong criterion syntax
Comparison operators must be inside the quotes, joined with the value:
// WRONG — the > is outside the string
=SUMIF(E2:E8, > 1000)
// CORRECT
=SUMIF(E2:E8, ">1000")
// ALSO CORRECT — using a cell reference
=SUMIF(E2:E8, ">"&H1)Dates stored as text
If your date column contains text strings that look like dates (common after CSV imports), date criteria won't work. Check by selecting a date cell — the formula bar should show a date serial number or proper date format, not a plain string. Use DATEVALUE() to convert text dates to real dates.
Criteria with special characters
If your data contains asterisks or question marks as literal characters, prefix them with a tilde in your criterion:
// Count cells containing a literal asterisk
=COUNTIF(A2:A8, "*~**")SUMIF vs COUNTIF vs AVERAGEIF — Quick Comparison
Google Sheets has a full family of conditional functions. Here's how they compare:
| Function | Purpose | Syntax | Returns |
|---|---|---|---|
SUMIF |
Sum values matching one criterion | SUMIF(range, criterion, [sum_range]) |
Total of matching values |
COUNTIF |
Count cells matching one criterion | COUNTIF(range, criterion) |
Number of matches |
AVERAGEIF |
Average values matching one criterion | AVERAGEIF(range, criterion, [average_range]) |
Mean of matching values |
SUMIFS |
Sum values matching multiple criteria | SUMIFS(sum_range, range1, criterion1, ...) |
Total of matching values |
COUNTIFS |
Count cells matching multiple criteria | COUNTIFS(range1, criterion1, ...) |
Number of matches |
AVERAGEIFS |
Average values matching multiple criteria | AVERAGEIFS(avg_range, range1, criterion1, ...) |
Mean of matching values |
MINIFS |
Minimum value matching criteria | MINIFS(range, range1, criterion1, ...) |
Smallest matching value |
MAXIFS |
Maximum value matching criteria | MAXIFS(range, range1, criterion1, ...) |
Largest matching value |
Key differences to remember:
- Argument order changes between SUMIF and SUMIFS. In
SUMIF, the sum range is the last argument. InSUMIFS, it's the first. - SUMIF/COUNTIF accept exactly one criterion. For two or more conditions, switch to the plural versions.
- AVERAGEIF ignores blank cells and cells with text in the average range — same as
AVERAGEitself. - MINIFS/MAXIFS were added later and only support the multi-criteria syntax (no single-criterion MINIF/MAXIF exists).
A quick example using AVERAGEIF on our dataset:
=AVERAGEIF(A2:A8, "Widget", E2:E8)Returns 1266.67 — the average sale amount for Widget (3800 / 3).
Frequently Asked Questions
What is the difference between SUMIF and SUMIFS?
SUMIF handles a single condition. SUMIFS handles multiple conditions simultaneously with AND logic. They also have different argument orders: SUMIF(range, criterion, sum_range) vs. SUMIFS(sum_range, range1, criterion1, range2, criterion2, ...). Use SUMIFS whenever you have two or more conditions.
How do I use COUNTIF with multiple criteria?
Switch to COUNTIFS (plural). Each additional criterion gets its own range/criterion pair:
=COUNTIFS(A2:A8, "Widget", F2:F8, "Closed")All conditions use AND logic — every criterion must be true for a row to be counted. For OR logic, add separate COUNTIF calls or use SUMPRODUCT.
Can SUMIF handle OR conditions?
Not directly. SUMIF and SUMIFS only support AND logic. For OR conditions, add multiple SUMIF calls together:
=SUMIF(A2:A8, "Widget", E2:E8) + SUMIF(A2:A8, "Gizmo", E2:E8)For more complex OR logic, use SUMPRODUCT:
=SUMPRODUCT(((A2:A8="Widget")+(B2:B8="South")>0)*E2:E8)How do I count non-blank cells in Google Sheets?
Three options:
=COUNTA(F2:F8) // Dedicated function, simplest
=COUNTIF(F2:F8, "<>") // COUNTIF approach, same result
=ROWS(F2:F8) - COUNTBLANK(F2:F8) // Subtract blanks from totalCOUNTA is the go-to for most cases. Use COUNTIF when you want to exclude specific values alongside the non-blank check.
What wildcards work in SUMIF and COUNTIF?
Google Sheets supports two wildcards:
*matches any number of characters (including zero)."G*"matches "Gadget", "Gizmo", "G".?matches exactly one character."G???o"matches "Gizmo" but not "Gadget".~escapes a wildcard."~*"matches a literal asterisk character.
Wildcards only work with text criteria. They have no effect on numeric comparisons like ">1000".
How do I make SUMIF case-sensitive?
SUMIF is always case-insensitive — there's no flag to change this. For case-sensitive conditional summing, use SUMPRODUCT with EXACT:
=SUMPRODUCT(EXACT(A2:A8, "Widget")*E2:E8)The EXACT function performs a case-sensitive comparison on each cell. SUMPRODUCT then multiplies the TRUE/FALSE results by the amounts and sums everything up.
