If you work with data in spreadsheets, counting values based on conditions is one of the most common tasks you’ll encounter. That’s where COUNTIF in Google Sheets shines. It allows you to count how many cells meet a specific rule—such as how many times a value appears, how many numbers exceed a threshold, or how many cells contain certain text.
In this guide, you’ll learn exactly how COUNTIF works in Google Sheets, when to use it, how it differs from similar functions, and how to avoid common mistakes. Whether you’re brand new to spreadsheets or refining an existing workflow, this article is designed to be practical, clear, and easy to apply.
What does COUNTIF do in Google Sheets?
COUNTIF counts the number of cells in a range that meet a single condition. That condition can be a number, text, date, or even a logical rule like “greater than” or “contains.”
Instead of scanning a column manually or using filters, COUNTIF lets you answer questions like:
How many sales were above $1,000?
How many tasks are marked “Complete”?
How many times does a customer name appear?
The function evaluates each cell in the selected range and increases the count only when the condition is met.
How is COUNTIF different from COUNT and COUNTA?
Understanding the difference between these functions helps prevent confusion and incorrect results.
COUNT counts only numeric values and ignores text and blanks.
COUNTA counts all non-empty cells, regardless of what’s inside them.
COUNTIF counts cells that meet a specific condition you define.
If you need logic or rules involved, COUNTIF is the right choice. If you just want a total number of filled cells, COUNTA is usually better.
What is the COUNTIF syntax in Google Sheets?
COUNTIF uses a simple two-argument structure:
The range you want to evaluate.
The condition that determines what gets counted.
The general format is:
COUNTIF(range, criterion)
The range can be a single column, a row, or a multi-cell selection. The criterion defines what qualifies as a match.
COUNTIF Formula Examples
Below are the most common and useful COUNTIF patterns. These examples are intentionally grouped so you can quickly find the type of condition you need.
Count cells equal to a specific value
=COUNTIF(A2:A50,”Yes”)
This counts how many cells in A2 through A50 contain the word “Yes.”
Count numbers greater than or less than a value
=COUNTIF(B2:B50,”>100″)
=COUNTIF(B2:B50,”<=25″)
Logical operators must be wrapped in quotes. This is one of the most common beginner mistakes.
Count cells that contain text
=COUNTIF(C2:C50,”apple“)
The asterisks are wildcards. This formula counts any cell that contains the word “apple” anywhere in the text.
Count blank or non-blank cells
=COUNTIF(D2:D50,””)
=COUNTIF(D2:D50,”<>”)
An empty string (“”) counts blank cells. The <> operator means “not equal to,” so it counts non-blank cells.
Count dates that meet a condition
=COUNTIF(E2:E50,”>=1/1/2025″)
Dates are treated as numbers in Google Sheets. Always use a consistent date format or reference a date cell for accuracy.
How do criteria rules work in COUNTIF?
The criterion is always evaluated as text, even when working with numbers. That’s why operators like >, <, and <> must be inside quotation marks.
You can also reference another cell in the criterion:
=COUNTIF(A2:A50,”>”&G1)
This counts values greater than the number in cell G1. This dynamic approach is especially useful for dashboards and templates.
Can COUNTIF handle case-sensitive text?
No. COUNTIF is not case-sensitive. It treats “Apple,” “apple,” and “APPLE” as the same value.
If you need case-sensitive counting, you must use a combination of ARRAYFORMULA and EXACT, which is outside the scope of basic COUNTIF usage but important to know as a limitation.
This limitation is rarely mentioned in beginner guides, yet it can cause subtle counting errors in datasets like usernames, IDs, or codes.
What are the most common COUNTIF mistakes?
Many COUNTIF issues come from small syntax errors or misunderstandings. Here are the most frequent ones.
Using operators without quotes.
For example, =COUNTIF(A:A,>10) will fail. The correct version is =COUNTIF(A:A,”>10″).
Forgetting wildcards when counting partial text.
Without * characters, COUNTIF looks for an exact match.
Mixing COUNTIF with multiple conditions.
COUNTIF supports only one criterion. Trying to force multiple conditions into it leads to incorrect results.
Referencing mismatched ranges.
COUNTIF requires a single contiguous range. You can’t combine unrelated columns without more advanced formulas.
When should you use COUNTIFS instead of COUNTIF?
COUNTIF works with one condition. COUNTIFS is designed for multiple conditions across one or more ranges.
For example, if you want to count orders where:
Status is “Complete”
AND Region is “West”
You must use COUNTIFS, not COUNTIF.
A helpful rule of thumb:
If you say “and” or “also” when describing your logic, you probably need COUNTIFS.
How does COUNTIF behave with filtered data?
COUNTIF ignores filters. It counts all cells in the range, even if some rows are hidden by a filter.
This surprises many users building reports. If you need counts that respect filters, you must use SUBTOTAL or database-style functions instead.
This behavior is consistent across Google Sheets and Excel, but it’s rarely explained clearly in basic tutorials.
Can COUNTIF be used with entire columns?
Yes. You can safely use:
=COUNTIF(A:A,”Yes”)
Google Sheets handles full-column references efficiently. However, for very large or complex spreadsheets, limiting the range can improve performance slightly.
In templates meant for public use, full-column ranges are often preferable because they automatically adapt as users add new data.
How COUNTIF fits into real-world spreadsheets
COUNTIF is most powerful when combined with clean data structure. It works best when:
Each column has one type of data.
Headers are excluded from ranges.
Text values are standardized (no extra spaces or inconsistent spelling).
In dashboards, COUNTIF is often paired with charts to display totals dynamically. In trackers and logs, it helps summarize activity without manual counting.
Advanced tip: COUNTIF and helper columns
If you find yourself needing complex logic, a helper column can simplify your formulas. Instead of forcing everything into COUNTIF, calculate a TRUE/FALSE result in another column, then count the TRUE values.
This approach improves readability, reduces errors, and makes your spreadsheet easier for others to understand—especially when sharing templates or downloadable resources.
When COUNTIF isn’t the best solution
COUNTIF is simple by design. It’s not meant to replace:
Pivot tables for large summaries.
QUERY for advanced filtering.
ARRAYFORMULA for complex transformations.
Knowing when not to use COUNTIF is just as important as knowing how to use it.
Final thoughts on COUNTIF in Google Sheets
COUNTIF is one of the most useful foundational functions in Google Sheets. It’s easy to learn, flexible enough for real-world use, and powerful when applied thoughtfully.
Once you understand how criteria work, how text matching behaves, and where COUNTIF’s limitations are, you can build cleaner spreadsheets, more reliable reports, and smarter templates.
If you’re building dashboards, trackers, or reusable tools for others, mastering COUNTIF is a must—and it pairs perfectly with more advanced functions as your skills grow.
For more practical spreadsheet guides and ready-to-use templates, explore the resources available on Sheetrix and start applying these techniques immediately.
