How to Use the COUNTIF Google Sheets Function in 2024
In this article, you'll learn how to use the COUNTIF function in Google Sheets.
Here's what you'll learn —
How to Count Cells Containing Specific Text
How to Count Cells Greater Than or Less Than a Value
How to Count Blank Cells
Common Mistakes When Using COUNTIF in Google Sheets
How to use COUNTIF in Rows [Easier Alternative]
What is the COUNTIF Function in Google Sheets?
The COUNTIF function in Google Sheets is useful for counting cells that meet a specific criterion. It returns a conditional count across a range.
Syntax
COUNTIF(range, criterion)
range - The range that is tested against the criterion.
criterion - The pattern or test to apply to the range.
If the range contains text to check against, the criterion must be a string. Criterion can contain wildcards including ? to match any single character or * to match zero or more contiguous characters. To match an actual question mark or asterisk, prefix the character with the tilde (~) character (i.e., ~? and ~*). A string criterion must be enclosed in quotation marks. Each cell in the range is then checked against the criterion for equality (or match, if wildcards are used).
If the range contains numbers to check against, the criterion may be a string or a number. If a number is provided, each cell in the range is checked for equality with the criterion. Otherwise, a criterion may be a string containing a number (which also checks for equality) or a number prefixed with any of the following operators: =, >, >=, <, or <=, which check whether the range cell is equal to, greater than, greater than or equal to, less than, or less than or equal to the criterion value, respectively.
The spreadsheet where data comes to life
Connected to your business data. Powered by AI. Delightful to share.
Try Rows for FreeHow to use COUNTIF in Google Sheets
The COUNTIF function in Google Sheets allows you to count the number of cells in a range that meets a specified criterion.
Here's how to use it effectively:
The basic syntax for COUNTIF is:
=COUNTIF(range, criterion)
Where:
range is the range of cells you want to count
criterion is the condition you're counting
How to Count Cells Containing Specific Text
Open your Google Sheet and input the formula into a cell. Replacing with your text criterion. Then press enter.
For example, the formula:
=COUNTIF(range, "criterion")
Will be:
=COUNTIF(A1:A10, "Apple") if you want to count how many cells in A1:A10 contain exactly "Apple.”
How to Count Cells Greater Than or Less Than a Value
1. Select your desired cell. This is where you want the result to appear.
2. Enter the formula
=COUNTIF(range, numerical criterion with a comparison operator (e.g., ">80") and press Enter.
For example:
=COUNTIF(B1:B20, ">80")
This counts how many cells in B1:B20 contain a number greater than 100.
The result is 3, which is showing slightly above the formula.
How to Count Blank Cells
While the formula for COUNTIF is =COUNTIF(range, "criterion")
The process is a little different if you want to count blank cells. Select your desired cell range. Next, input the formula, but “ as your criterion, and press Enter.
For example,
=COUNTIF(C1:C30, "")
This counts how many cells in C1:C30 are blank. The result for this example is 2.
Common Mistakes When Using COUNTIF in Google Sheets
COUNTIF is a powerful function in Google Sheets, but users often encounter several pitfalls. Understanding these common mistakes can help you use COUNTIF more effectively and troubleshoot issues when they arise.
1. Incorrect Syntax
Google Sheets uses commas to separate arguments in functions, regardless of regional settings.
Correct syntax:
=COUNTIF(A1:A10, "Apple")
A Common mistake is using semicolons instead of commas.
For example,
=COUNTIF(A1:A10; "Apple")
This output gives an error message.
Always double-check your syntax to ensure you're using commas correctly.
2. Misapplication of Criteria
This mostly results from not using wildcards for partial matches. When searching for partial matches, use wildcards to ensure you count all relevant data.
Incorrect: =COUNTIF(A1:A10, "App")
Correct: =COUNTIF(A1:A10, "App*")
The asterisk (*) wildcard allows for matching any characters after "App.”
3. Range Selection Errors
People sometimes skip a relevant cell while selecting a cell range. Always double-check your range selection to ensure you're counting the intended data.
4. Incorrect use of logical operators
Google Sheets doesn't support AND or OR operators directly in COUNTIF. Use COUNTIFS for multiple criteria.
Incorrect: =COUNTIF(A1:A10, ">5 AND <10")
Correct: =COUNTIFS(A1:A10, ">5", A1:A10, "<10")
COUNTIFS allows you to specify multiple conditions across multiple ranges.
5. Wrong Case
COUNTIF in Google Sheets is case-insensitive by default, which can lead to unexpected results if you're trying to distinguish between uppercase and lowercase.
For example,
=COUNTIF(A1:A10, "Apple")
The exact string “Apple” only appears 3, but COUNTIF counted 8 occurrences.
To make our results case-sensitive in Google Sheets, we have to combine them with ARRAY FORMULA and EXACT functions.
Say you prefer an exact string match; use the EXACT function:
=countif(ArrayFormula(EXACT(range,criterion)),TRUE)
where
the range is the set of cells
criterion is the value to find and count across the data range
See this in the example below:
6. Not using QUERY for large data sets
Large datasets can slow down COUNTIF. Consider using QUERY for better performance on big data:
Compared to COUNTIF, QUERY can be more flexible for complex conditions and often performs faster on large datasets.
QUERY is a powerful function in Google Sheets that allows you to perform database-like queries on your data.
QUERY syntax: =QUERY(data, query, [headers])
For example,
COUNTIF: =COUNTIF(A1:A1000,` "Apple")
QUERY equivalent: =QUERY(A1:A1000, "SELECT COUNT(A) WHERE A = 'Apple'")
The QUERY version may process faster because It's optimized for large data operations
When working with thousands of rows, consider using QUERY instead of COUNTIF for better performance.
To avoid these common mistakes:
1. Double-check your syntax, ensuring you're using commas to separate arguments.
2. Be aware of case sensitivity and use EXACT when necessary.
3. Pay attention to data types, especially with dates and numbers stored as text.
4. Carefully select your range and consider using named ranges for clarity.
5. For complex criteria, use COUNTIFS or array formulas with ARRAYFORMULA.
6. Use Google Sheets' built-in functions like QUERY for advanced counting tasks.
7. Consider performance implications when working with large datasets.
The spreadsheet where data comes to life
Connected to your business data. Powered by AI. Delightful to share.
Try Rows for FreeDealing with Non-Exact Matches
COUNTIF is designed to count cells that meet a specific criterion. However, it can sometimes produce unexpected results due to non-exact matches. This often happens because of:
Data type mismatches (e.g., numbers stored as text)
Hidden characters or formatting issues
Partial matches when dealing with text strings
Rounding differences with decimal numbers
Numeric values stored as text
Counting dates (Dates can be particularly tricky due to different date formats in Google Sheets and Excel).
To ensure accurate counts, here are some methods you can apply.
1. Using wildcards:
For partial string matches, can you use wildcards like * (any number of characters)? (single character).
Example: =COUNTIF(A1:A10, "*apple*") will count cells containing "apple" anywhere in the text.
2. Combining COUNTIF with other functions:
EXACT(): For strict text comparisons
VALUE(): To convert text to numbers
Example: COUNTIF(A1:A10, EXACT(A1:A10, "apple")
3. Using SUMPRODUCT for more complex criteria:
SUMPRODUCT can handle multiple conditions and is more flexible than COUNTIF.
Example: SUMPRODUCT(--(A1:A10="apple"), --(B1:B10>5))
4. Cleaning your data:
Use functions like TRIM() to remove extra spaces or CLEAN() to remove non-printable characters.
5. Dealing with numeric values stored as text:
Use the VALUE() function or multiply by 1 to convert text to numbers for correct numerical comparisons.
Example: COUNTIF(A1:A10*1, ">100")
6. Addressing rounding issues with decimals:
Use the ROUND() function or adjust the comparison to avoid mismatches due to small differences in decimal places.
Example: COUNTIF(ROUND(A1:A10,2), 3.14)
Practical Applications of COUNTIF in Real-Life Scenarios
The COUNTIF function in Google Sheets is a powerful tool that can be applied across various fields. Let's explore some practical applications using the sample data provided in the Excel sheets.
Application in the Education Sector
In the education sector, COUNTIF can be invaluable for analyzing student performance, grading, and attendance tracking.
Here are some examples:
Counting Students with High Math Grades
You can use the `COUNTIF` function to count how many students within a specific range scored above 85 in Math.
=COUNTIF(C2:C21, ">85")
Counting Students with Excellent Attendance
Teachers can use this formula to check attendance in class.
=COUNTIF(A1:J21, ">=95")
In this example, 11 students have a 95% or higher attendance rate.
Application for Budget Tracking, Project Management, and Event Planning
COUNTIF can significantly enhance project management processes by providing quick summaries of task statuses, budget allocations, and event participation.
Examples
Counting Completed Projects:
=COUNTIF(G2:G6, "Completed")
In this example, we use the `COUNTIF` function to count the number of projects that have been marked as Completed.
Application for Sales and Marketing
Sales managers can use the COUNTIF formula to efficiently assess team performance, product popularity, and regional trends.
Some examples:
Count Sales by a Specific Salesperson
=COUNTIF(B2:B6, "Alice")`
This example counts how many times "Alice" appears in the "Salesperson" column, which is in range B2:B6.
Count Sales for a Specific Product
This formula counts the number of times "Widget A" appears in the "Product Name" column, which is in the range E2:E6.
=COUNTIF(E2:E6, "Widget A")
Count Sales in a Specific Region
This example counts the number of sales made in the "North" region, which is in the range C2:C6.
Formula =COUNTIF(C2:C6, "North")
Inventory Management
You can use the COUNTIF syntax to count products from a specific supplier.
Formula: =COUNTIF(D2:D6, "Supplier X")
This example counts how many products are supplied by "Supplier X" in the "Supplier" column, which is in the range D2:D6.
Count Products Below Reorder Level
Formula: =COUNTIF(F2:F6, "<50")
This formula counts the number of products where the "Stock Level" is below 50, which is in the range F2:F6.
How to use COUNTIF in Rows [Easier Alternative]
Rows is a comprehensive spreadsheet for modern teams that offers a better interface, built-in integrations for data ingestion, and native AI capabilities (AI analyst, AI-generated subtitles, native AI functions).
Rows is similar to Google Sheets when it comes to executing the COUNTIF function for counting cells containing specific text and cells with a higher or lower value than a criterion.
However, compared to Google Sheets, Rows has AI-powered features that can help you make the most of your spreadsheet.
But before we discuss that, let's give you a sneak peek of the tidy nature of Rows when it comes to the COUNTIF function —
Counting cells containing specific text in Rows
Follow the steps above for Google Sheets. Type the COUNTIF syntax in an empty cell to draft the value you want. In this case, we check the number of times “David” appears in the A column.
After inserting the syntax, click the “enter” tab on your keyboard to see the result.
How to Count Cells Greater Than or Less Than a Value in Rows
With Rows, you can count cells greater than or less than a specific value. All you need to do is insert the syntax.
Click “enter”. And here we go — characters greater than 300 appeared four times in the C column of the spreadsheet.
The spreadsheet powered by AI
Count cells without using syntax. Summarize, transform, and aggregate your data using AI Analyst.
Try Rows for FreeIt's time to skip the COUNTIF formula — What makes Rows better than Google Sheets?
The sweetest part of using Rows is that you can count cells for any specific criterion without using the syntax.
How? You ask.
It's simple! We have an AI analyst. This AI Analyst analyzes, summarizes, and transforms your data by granting you access to deeper insights.
Click on the "AI Analyst“ ✨ icon, at the top right corner of any table.
A chat interface will open on the right: you can ask a broad range of questions, from basic spreadsheet commands - count elements that meet a specific condition - to more complex tasks, such as slicing, pivoting, or computing metrics about your data.
For example, I started by asking — How many times does “David” appear in Column A?
Guess what? It didn't disappoint. In a split second, I got an answer.
That was easy. Let's now take it up a notch. We have a dataset with daily revenue and costs of various marketing campaigns, you can ask the Analyst to add a column with the profit margin. Watch the video below:
In addition, our AI Analyst is instructed to use our native OpenAI functions to perform data enrichment or extraction tasks.
For example, you can ask the AI analyst to run a sentiment analysis on a column with product reviews, or add a column that categorizes addresses into regions, see below:
With our AI analyst, you don't need to worry about executing syntax for counting specific cells — it can often lead to errors and costly mistakes. Check out our guide or watch our demo.
What other features make Rows better?
Data aggregation — OpenAI integration
You can aggregate live data from 50+ sources in a very simple manner, thanks to built-in data integrations in various domains:
Marketing: GA4, GSC, Facebook, Instagram, TikTok, etc
Productivity software: OpenAI, Notion, Slack, Email, Translate
Data Warehouse: MySQL, BigQuery, PostgreSQL, Snowflake, Amazon Redshift
And many more.
See the example below:
Modern UI
Spreadsheets in Rows present a document-like layout with standalone tables and charts stacked vertically and horizontally.
WYSIWYG: the spreadsheet IS the dashboard. All data is rendered as regular spreadsheet tables. You can move elements vertically and horizontally with drag and drop. And if something breaks, you can intervene on that table or chart flexibly, without the need to dive into complex workflows.
View Mode: View mode makes your spreadsheets work like web applications. With input fields, you can help anyone understand how to use your spreadsheet by highlighting the cells that need an input by the user.
Supercharge your sheets with Rows
With Rows, you get access to 50+ built-in data source integrations in various domains.
This makes it easy to ingest data into your table quickly and reduces your team's stress. With Rows, you can gather data from various sources in your system. This allows for more detailed analyses and helps your team uncover insights they might miss.
Ready to get started with Rows.com? Start using the product right away for free.
