COUNTIF Function in Google Sheets: Complete Guide
COUNTIF is the workhorse function for conditional counting in Google Sheets. It answers one simple question: 'How many cells in this range meet my criterion?' Whether you're tracking how many sales...
Key Insights
- COUNTIF counts cells matching a single criterion, while COUNTIFS handles multiple conditions—master both to avoid convoluted nested formulas
- Wildcards (* and ?) transform COUNTIF from a simple matching tool into a powerful pattern-recognition function for text data
- Most COUNTIF errors stem from mixing text and numbers or forgetting to wrap operators in quotes when using cell references
Introduction to COUNTIF
COUNTIF is the workhorse function for conditional counting in Google Sheets. It answers one simple question: “How many cells in this range meet my criterion?” Whether you’re tracking how many sales exceeded a threshold, counting team members in a specific department, or tallying responses matching a particular answer, COUNTIF handles it.
Before reaching for COUNTIF, understand when simpler alternatives work better. COUNT tallies only numeric values—useful when you just need to know how many numbers exist in a range. COUNTA counts all non-empty cells regardless of content type. COUNTIF enters the picture when you need conditional logic: count only the cells that match specific criteria.
The function shines in data validation, dashboard metrics, and quick data exploration. Once you internalize its syntax, you’ll find yourself using it constantly.
Basic Syntax and Parameters
The COUNTIF formula follows a straightforward structure:
=COUNTIF(range, criterion)
Range specifies which cells to evaluate. This can be a simple range like A1:A100, an entire column like B:B, or a named range. The range typically contains the data you’re analyzing.
Criterion defines what you’re looking for. This can be a number, text string, cell reference, or expression with comparison operators. Text criteria must be wrapped in double quotes.
Here’s a basic example counting cells equal to a specific number:
=COUNTIF(A1:A50, 100)
This counts every cell in A1:A50 that contains exactly 100. For text matching:
=COUNTIF(B1:B100, "Completed")
This counts cells containing the exact text “Completed”. Note the quotes around the text criterion—they’re required. The match is case-insensitive by default, so “completed”, “COMPLETED”, and “Completed” all match.
For counting blank cells:
=COUNTIF(C1:C50, "")
And for non-blank cells:
=COUNTIF(C1:C50, "<>")
Using Comparison Operators
Raw equality matching only gets you so far. Comparison operators unlock COUNTIF’s analytical power by letting you count cells above thresholds, below limits, or excluding specific values.
The available operators are:
>greater than<less than>=greater than or equal to<=less than or equal to<>not equal to
When using operators, wrap the entire criterion in quotes:
=COUNTIF(A1:A100, ">50")
This counts all values greater than 50. For counting non-zero values:
=COUNTIF(B1:B50, "<>0")
Combining operators with values requires the operator and value together in the same quoted string:
=COUNTIF(C1:C200, ">=1000")
This counts cells with values of 1000 or higher—useful for threshold analysis like counting orders above a minimum amount.
For date comparisons, the same operators work:
=COUNTIF(D1:D100, ">2024-01-01")
However, date handling can be tricky. I’ll cover the gotchas in the troubleshooting section.
Wildcards for Partial Matching
Text data rarely cooperates with exact matching. Product codes have prefixes, names have variations, and categories include subcategories. Wildcards solve this by enabling pattern matching.
Asterisk (*) matches any sequence of characters, including zero characters:
=COUNTIF(A1:A500, "Pro*")
This counts cells starting with “Pro”—matching “Product”, “Professional”, “Project”, and “Pro” itself.
To count cells containing a substring anywhere:
=COUNTIF(B1:B200, "*error*")
This finds “error”, “Error message”, “Network error occurred”, or any text containing “error”.
Question mark (?) matches exactly one character:
=COUNTIF(C1:C100, "???-####")
This matches patterns like “ABC-1234” or “XYZ-9999”—three characters, a hyphen, then four characters.
Combine wildcards for complex patterns:
=COUNTIF(D1:D300, "SKU-*-2024")
This matches “SKU-WIDGET-2024”, “SKU-A1-2024”, or any SKU ending with 2024.
For counting cells ending with specific text:
=COUNTIF(E1:E150, "*.pdf")
This counts all PDF filenames in a list.
Dynamic Criteria with Cell References
Hardcoded criteria work for one-off analysis, but real spreadsheets need flexibility. Reference cells to make your COUNTIF formulas dynamic and user-controllable.
Simple cell reference for the criterion:
=COUNTIF(A:A, D1)
Whatever value sits in D1 becomes the criterion. Change D1, and the count updates automatically. This pattern is essential for building interactive dashboards.
When combining operators with cell references, concatenation is required:
=COUNTIF(B:B, ">"&E1)
The ampersand joins the operator string with the cell value. If E1 contains 500, the formula evaluates as =COUNTIF(B:B, ">500").
For “not equal to” with a cell reference:
=COUNTIF(C1:C100, "<>"&F1)
This counts cells not matching whatever F1 contains.
Build a reusable threshold counter by placing your operator in one cell and value in another:
=COUNTIF(A:A, G1&H1)
If G1 contains “>=” and H1 contains 100, you get a configurable threshold counter.
For wildcard patterns with cell references:
=COUNTIF(B:B, "*"&J1&"*")
This counts cells containing whatever text J1 holds.
COUNTIFS for Multiple Criteria
Single-criterion counting has limits. When you need AND logic—count cells meeting multiple conditions simultaneously—COUNTIFS is your tool.
The syntax extends naturally:
=COUNTIFS(range1, criterion1, range2, criterion2, ...)
Each range-criterion pair adds another filter. Only cells passing all criteria get counted.
Count sales over $100 in the East region:
=COUNTIFS(A1:A500, "East", B1:B500, ">100")
Column A contains regions, column B contains amounts. Only rows where both conditions are true increment the count.
For date range analysis, use two criteria on the same column:
=COUNTIFS(C1:C200, ">=2024-01-01", C1:C200, "<=2024-03-31")
This counts dates within Q1 2024.
Combine text and numeric conditions:
=COUNTIFS(D1:D1000, "Active", E1:E1000, ">0", F1:F1000, "<>Error")
This counts active records with positive values that don’t have errors.
COUNTIFS with cell references follows the same concatenation rules:
=COUNTIFS(A:A, G1, B:B, ">"&H1, C:C, I1)
All three criteria are now dynamic.
Common Errors and Troubleshooting
COUNTIF failures usually trace back to a few recurring issues. Here’s how to diagnose and fix them.
Text stored as numbers (or vice versa): A cell displaying “100” might contain the number 100 or the text “100”. COUNTIF treats these differently. If your count seems wrong, check the data type. The formula =ISNUMBER(A1) returns TRUE for actual numbers. Convert text to numbers with =VALUE() or multiply by 1.
Date formatting mismatches: Dates in Google Sheets are numbers formatted for display. When your COUNTIF date comparison fails, the criterion might not match the underlying date value. Use the DATE function for reliable comparisons:
=COUNTIF(A1:A100, ">"&DATE(2024,1,1))
Missing quotes around operators: This breaks the formula:
=COUNTIF(A:A, >50)
This works:
=COUNTIF(A:A, ">50")
Operators must be quoted strings.
Escaping literal wildcards: What if you need to count cells containing an actual asterisk or question mark? Precede the wildcard with a tilde (~):
=COUNTIF(B1:B100, "*~**")
This counts cells containing a literal asterisk character.
Case sensitivity: COUNTIF is case-insensitive by default. “Apple”, “APPLE”, and “apple” all match the criterion “Apple”. If you need case-sensitive counting, combine SUMPRODUCT with EXACT:
=SUMPRODUCT((EXACT(A1:A100, "Apple"))*1)
Leading/trailing spaces: Invisible spaces break exact matches. Use TRIM on your data or incorporate wildcards:
=COUNTIF(A1:A50, "*"&B1&"*")
Empty criterion returning unexpected results: =COUNTIF(A:A, "") counts blank cells, but if your range includes the formula cell itself, you might get circular reference errors. Specify explicit ranges instead of entire columns when the formula lives in that column.
COUNTIF and COUNTIFS form the foundation of conditional analysis in Google Sheets. Master the syntax, understand the operator quoting rules, and leverage wildcards for text patterns. These functions will handle 90% of your counting needs without requiring complex array formulas or scripts.