Don’t forget to share this post

You may need to count cells in Google Sheets for many reasons. However, the COUNT function is somewhat limited, as you are usually interested in counting cells that meet specific criteria. While it‘s possible to write nested functions with COUNT and IF, there’s no need for that. Google Sheets has built-in functions to save you the trouble: COUNTIF and COUNTIFS. The first allows you to specify a range of cells and the criterion that cells must meet to be counted. COUNTIFS, however, allows you to specify multiple ranges and multiple criteria.

In this post, you will learn about the COUNTIF and COUNTIFS functions in Google Sheets. After reviewing the syntax for both functions, you will learn how to use them in different circumstances. You have a variety of examples with step-by-step instructions using different types of conditions, such as counting non-blank cells, cells containing partial or specific text, cells with values greater than or equal to a particular value, and cells with values within a range. Additionally, you will learn how to use the COUNTIFS function to count values based on multiple criteria.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

COUNTIF & COUNTIFS Functions Syntax in Google Sheets

Let’s review the syntax for COUNTIF and COUNTIFS before using them.

COUNTIF Function Syntax

Google Sheets’ COUNTIF function has two required parameters.

=COUNTIF(range, criterion)
  • range: the range of cells that you want to test using the criterion.
  • criterion: the test or condition that you want to apply to the range.

COUNTIFS Function Syntax

Google Sheets’ COUNTIFS function has two required parameters, just like COUNTIF. However, COUNTIFS allows you to provide additional parameters to test multiple ranges using different criteria.

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

The first range and the first criterion are required arguments.

  • criteria_range1: the first range of cells that you want to test using the first criterion.
  • criterion1: the test or condition that you want to apply to the first range.

Additional ranges and criteria are optional, but you can add as many as you want. However, the dimensions of subsequent ranges must be equal to those of the first range - all ranges should have the same number of rows and columns.

  • criteria_range2*: the second range of cells that you want to test using the second criterion.
  • criterion2*: the test or condition you want to apply to the second range.

How to Build the COUNTIF Criteria or Conditions?

There are many useful operators you can use to define the criteria or conditions in the COUNTIF and COUNTIFS functions. For example, you can use the following criteria to search for non-blank and blank cells, respectively.

=COUNTIF(range, “<>”)
=COUNTIF(range, “”)

You can also use the Boolean operators TRUE and FALSE to count a cell based on the results of a formula or to count checked or unchecked checkboxes.

Numeric Values

When dealing with exact numeric values, you don’t need to use quotes. However, if you're using comparison operators, you’ll need the quotes to enclose the condition or criterion.

  • <> not equal
  • = equal
  • >= greater than or equal
  • <= less than or equal
  • > greater than
  • < less than

Text Values

You also have a lot of options in terms of text values. In addition to matching exact text by using quotation marks, you can also use any of the following wildcards for partial matches.

  • * Matches 0 or more characters.
  • ? Matches a single character
  • ~ If you need to search for the symbols “*” or “?”, add the tilde before the symbol to escape it, so it's not used as a wildcard.

Examples of How to Use the COUNTIF Function in Google Sheets

Now that you have reviewed the syntax for the COUNTIF function, let's see some examples of how you can use it in different situations.

Imagine you have a table with sales data, which includes the name of the sales agent, their team, the product, and the sales amount. You need to get different counts for your sales report, but counting manually would be a nightmare, as the table has five hundred rows.

COUNTIF in Google Sheets Formula Examples Sales Data
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Sales Data

Google Sheets COUNTIF Cell Contains Text

Follow the steps below to count cells that contain specific text. In this case, let's count the number of sales of ‘productA’.

  1. 1. In an empty cell, type in the COUNTIF formula.
COUNTIF in Google Sheets Formula Examples Add Formula
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Add COUNTIF Formula
  1. 2. Select the column with the product information as the ‘range’.
COUNTIF in Google Sheets Formula Examples Select Range
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Select Range
  1. 3. For the criterion, use “productA”.
COUNTIF in Google Sheets Formula Examples Add Criterion
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Add COUNTIF Criterion
  1. 4. That’s it. The number of transactions related to this product is 112.
COUNTIF in Google Sheets Formula Examples COUNTIF Result
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - COUNTIF Result
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

Second, let's find the number of sales made by ‘Southteam’. Instead of using an exact match, you can use wildcards to search for part of the text.

  1. 1. In an empty cell, type in the function.
COUNTIF in Google Sheets Formula Examples Add Formula 2
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Add COUNTIF Formula
  1. 2. For the range, select the column with the team names.
COUNTIF in Google Sheets Formula Examples Select Range 2
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Select Range
  1. 3. For the criterion, the function should match cells containing ‘south’, followed by any number of characters.
COUNTIF in Google Sheets Formula Examples Add Criterion 2
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Add COUNTIF Criterion
  1. 4. That’s it. Southteam made a total of 76 sales.
COUNTIF in Google Sheets Formula Examples COUNTIF Result 2
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - COUNTIF Result

Google Sheets COUNTIF Greater Than

Imagine you want to count the number of sales with a value greater than $600.

  1. 1. Type the COUNTIF formula in an empty cell.
COUNTIF in Google Sheets Formula Examples Add Formula 3
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Add COUNTIF Formula
  1. 2. For the range, select the column with the sales amounts.
COUNTIF in Google Sheets Formula Examples Select Range 3
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Select Range
  1. 3. The criterion should check that values are greater than $600.
COUNTIF in Google Sheets Formula Examples Add Criterion 3
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Add COUNTIF Criterion
  1. 4. That’s it. There were 380 sales greater than $600.
COUNTIF in Google Sheets Formula Examples COUNTIF Result 3
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - COUNTIF Result

Google Sheets COUNTIF Not Blank

Let’s say you’ve noticed some blank rows in your dataset, and you want to count the number of sales. To count all non-blank cells in a range, follow the steps below.

  1. 1. In an empty cell, type in the COUNTIF formula.
COUNTIF in Google Sheets Formula Examples Add Formula 4
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Add COUNTIF Formula
  1. 2. Select the range of cells you want to check.
COUNTIF in Google Sheets Formula Examples Select Range 4
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Select Range
  1. 3. For the criterion, use “<>”, with the quotation marks.
COUNTIF in Google Sheets Formula Examples Add Criterion 4
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Add COUNTIF Criterion
  1. 4. That's it. All non-blank cells have been counted.
COUNTIF in Google Sheets Formula Examples COUNTIF Result 4
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - COUNTIF Result
How To Merge Google Sheets Manually Automatically
How To Merge Google Sheets (Manually & Automatically)

Oftentimes we need to combine data from multiple sheets into one master sheet. Here’s how to merge Google Sheets manually and automatically.

READ MORE

Examples of How to Use the COUNTIFS Function with Multiple Conditions in Google Sheets

In Google Sheets, you can use the COUNTIFS function to count cells based on multiple criteria, and each criterion can be applied to a different range. For example, you can count the sales of a specific product with values greater than a particular amount and values within a specific range.

Google Sheets COUNTIFS with Different Criteria for Different Ranges

Let’s find the number of sales of productA for an amount greater than $500.

  1. 1. In an empty cell, type in the COUNTIFS formula.
COUNTIF in Google Sheets Formula Examples Add Formula 5
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Add COUNTIFS Formula
  1. 2. Select the first range you want to evaluate. In this case, the column containing the products.
COUNTIF in Google Sheets Formula Examples First Range
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - COUNTIFS First Range
  1. 3. Add the criterion you want to apply to the first range. In this case, values should match ”productA”.
COUNTIF in Google Sheets Formula Examples First Criterion
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - COUNTIFS First Criterion
  1. 4. Next, select the second range you want to evaluate. For this example, it's the column with the sales amounts.
COUNTIF in Google Sheets Formula Examples Second Range
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - COUNTIFS Second Range
  1. 5. The second criterion should check for values greater than $500.
COUNTIF in Google Sheets Formula Examples Second Criterion
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - COUNTIFS Second Criterion
  1. 6. That’s it. There were 91 sales of ‘productA” greater than $500.
COUNTIF in Google Sheets Formula Examples COUNTIFS Result
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - COUNTIFS Result

Google Sheets COUNTIFS Between Two Numbers

You can also use COUNTIFS to count the cells with values within a specified range by applying different criteria to the same range. For example, sales amounts above $600 but below $1000.

  1. 1. Type the COUNTIF formula in an empty cell.
COUNTIF in Google Sheets Formula Examples Add Formula 6
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Add COUNTIFS Formula
  1. 2. Select the column with the sales amounts as the first range.
COUNTIF in Google Sheets Formula Examples First Range 2
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - First COUNTIFS Range
  1. 3. The first criterion is that the value has to be greater than $600.
COUNTIF in Google Sheets Formula Examples First Criterion 2
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - First COUNTIFS Criterion
  1. 4. For the second range, select the column with the sales amounts.
COUNTIF in Google Sheets Formula Examples Second Range 2
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Second COUNTIFS Range
  1. 5. The second criterion is that the values be under $1000.
COUNTIF in Google Sheets Formula Examples Second Criterion 2
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - Second COUNTIFS Criterion
  1. 6. That’s it. There were 103 sales between $600 and $1000.
COUNTIF in Google Sheets Formula Examples COUNTIFS Result 2
COUNTIF & COUNTIFS in Google Sheets: Formula & Examples - COUNTIFS Result

How to Automate Your Data on Top of Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

Conclusion

COUNTIF and COUNTIFS are very useful functions. You can count cells based on their contents by specifying a single criterion or multiple criteria. The criteria can be as simple or complex as you want and use logical operators, Boolean values, and wildcards.

You now know how to use COUNTIF and COUNTIFS to count cells containing numeric and Boolean values, as well as text. You also know how to write criteria to find numeric values greater than a specific number, numeric values within a range of values, exact matches for numeric and text values, partial matches for text using wildcards, and non-blank cells.

To learn more about the related function SUMIF, check out our post on SUMIF & SUMIFS in Google Sheets: Formula & Examples.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Dec 20 2022, Updated Dec 19 2022