All posts
Published at Mon Aug 19 2024 in
For Teams

How to Use the COUNTIF Google Sheets Function in 2024

Alberto Manassero, Product Growth Manager, Rows

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.

How 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.

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.

7. Consider performance implications when working with large datasets.

The spreadsheet where data comes to life

Dealing 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))

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.

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.

Count cells without using syntax. Summarize, transform, and aggregate your data using AI Analyst.

It'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.

It's simple! We have an AI analyst. This AI analyst analyzes, summarizes, and transforms your data by granting you access to deeper dives and insights.

Click on the “AI Analyst “ icon and you'll see tabs for “Quick insights” and “Deep dives”.

• Quick insights” lets you extract valuable insights from your data.

• The deep dives section is where the sauce is — it automatically detects all the cells and variables of your table, offering automated pivots, slices, and summaries of your data.

For example, I asked — How many times does “David” appear in Column A?

Guess what? It didn't disappoint. In a split second, I got an answer.

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.

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.