A Smooth Step-By-Step COUNTIF Google Sheets Guide

The COUNTIF Google Sheets function can be used when you want to get the count when a given condition is met. COUNTIF contains the Google Sheets COUNT function and the IF function. That’s why I created this in-depth guide. It shows you how to use the COUNTIF function with screenshots and practical examples.

That’s a Google Sheets hack you can use for all sorts of things. Before I show you some cool examples of using the COUNTIF Function, let’s quickly go through the syntax and some important things about it.

SYNTAX of the COUNTIF Google Sheets Function

Here, I discuss the structure of the COUNTIF function in Google Sheets. The syntax describes what you need to put inside the parenthesis to make the function work. Here’s an image that shows the syntax.

=COUNTIF(range, criterion)

• range – The range of cells from which you want to get the count.
• criterion – The condition to be met for a cell to be counted.

The criterion in the COUNTIF function could be:

• A number
• A text string, such as “Apple” or “Banana”
• A cell reference (such as A1 or B1)
• An expression such as “=100” or “>100” or “<100”. Note that in the case of equal to, you are not required to use the equal to sign. You can use the number in double quotes.

The range of cells could contain numbers or text strings:

• If the range contains numbers, then the criteria could use comparison operators (>, <, =, <>). For example, to count the number of students who scored above 75 in a subject, you can use “>75” as the criterion. The function would check each number in the ‘range’ for this criterion. Note: When you use an operator with a number, you must enclose it in double quotes.
• If the range contains text strings, the criteria can be the text string within double quotes (e.g., “Apple”) or can be a cell reference that contains the text string.
  • You can also use wildcard characters in case of text criterion. For example, to count cells that contain Apple or Apples, you can use Apple* as the criterion.

What Does the COUNTIF Google Sheets Function Do?

The COUNTIF function Google Sheets is used to count the number of times a criterion has been met in a range of cells. It looks for the exact matches of the criteria in the range of data and returns how many exact matches it finds.

That’s a pretty useful tool. For example, you could use it to quickly find out how many salespeople met quota already this quarter. You could determine how many individual SKUs are out of stock in a given moment, or you could highlight the most common survey response submitted via Google Forms.

Note that COUNTIF differs from the popular SUMIF formula. I actually did a deep dive on that subject, too. Check out my guide for how to use SUMIF in Google Sheets.

How To Use the COUNTIF Google Sheets Function

The COUNTIF Google Sheets function only has two arguments. Therefore, it can only be used with one criterion. The criteria can be a cell reference, a text string in quotation marks, or a wildcard character.

You can use the function to let Google spreadsheet count the number of cells with text by using empty quotation marks in the criteria.

You can also use operators in the criteria of the COUNTIF formula like > (greater than), < (less than), <> (not equal to), and = (equal to).

For example, you can count the numbers greater than 50 using the criteria >50.

Now, let’s have a look at some COUNTIF formula examples in Google Sheets.

COUNTIF Google Sheets Function Examples

One of the best ways to understand this type of function is by following along with a practical example. I wanted to highlight several ways to use COUNTIF, so I went a little overboard. Each of my examples includes screenshots that show explicitly how to pull the informatino.

Here are my six examples of using the COUNTIF Google Sheets functions.

Example 1 –  Counting the Number of Students with More than 75 Marks

Suppose you have a list of students with the marks scored on a test.

Here is the formula to give you the count of students who score above 75.

=COUNTIF($B$2:$B$14,">75")

Note:

• The condition needs to be within double quotes.
• You can also have the criterion value in a cell and use that cell reference. When you use a cell reference, you still need to have the operator in double quotes and use & to join it with the cell reference. For example, if the criterion is in cell C2, you can use the formula below:

  =COUNTIF($B$2:$B$14,">"&C2) 

Example 2 – How To Count Text Values in Google Sheets for Transaction Accounts

Suppose you have the transaction data as shown below:

Here is the formula that will give us the number of transactions for the US:

Note:

• Since the criterion is text, there is no need to use the equal to sign. In this example, the condition is that the country should be equal to the US so you can use “US.”
• You can also have the criterion in a cell. In that case, you can use the cell reference. For example, if the criterion is in cell D2, you can use the formula:

  =COUNTIF($B$2:$B$13,D2)

Example 3 – Counting All the Records Except US with a Google Sheet COUNTIF Function

For my third example, let’s talk about using COUNTIF with an exception. Suppose you have the same transaction data (used in example 3), and you want to count all the transaction records except for those of the US.

Here is the formula that will do this:

=COUNTIF($B$2:$B$13,"<>US")

The above formula would count all the transactions except the ones where the country is ‘US.’

Example 4 – Counting with Wildcard Characters

Wildcard characters include an asterisk (*) for whole words and a question mark (?) for a single character. For example, in our sheet below, we can count all the values with the word, Apple, in them.

To count the number of values that have the word apple in them, we have used the formula:

=COUNTIF(A2:A10, "Apple*")

We have the asterisk after the word Apple since all the values with the word Apple have it as the first word.

Example 5- Counting All Empty/Blank Cells in a Range with the COUNTIF Function Google Sheets Syntax

If you have a huge dataset and you want to get a count of missing records or empty or blank cells, you can use the Google spreadsheet COUNTIF functions to do this.

Suppose you have a dataset as shown below:

Here is the formula that will give you the total number of empty or blank cells in it:

=COUNTIF($C$2:$C$13,"")

In this example, the criteria used is ” ” (double quotes). When only double quotes are used as criteria, it counts only those cells that are empty/blank. Note: If there is a cell that looks blank but has a space character in it, then it will not be counted by this formula.

Example 6 – Counting All Non-blank Cells in a Range with the Google Sheets COUNTIF Function

You can use the function to make Google Sheets COUNTIF cell contain text. Taking the same data from example 4, in this example, let’s see how to count all the non-blank cells in the dataset.

Here is the formula that will count all the non-blank cells:

=COUNTIF($C$2:$C$13,"<>")

In this example, the criteria used is “<>” (not equal to a sign within double quotes). This criterion means that the cells are not equal to an empty string. This lets Google Sheets count the number of cells with text and return that number. Note: If there is a cell that looks blank but has a space character in it, then it will be counted by this formula.

Example 7- Count Cells Based on the Date

Using COUNTIF to count values based on dates works the same as with texts or strings. You must enclose the date in quotation marks or use a cell reference instead.

If not, you can also use DATE( ). For example, in our sheet below, we can count the number of values with dates before 20th Feb 2023 using the formula:

=COUNTIF(A2:A14,"<2/20/2023")

If you were to use the DATE function instead, then it needs to be in the format YYYY/MM/DD. You should not use quotations in this case. You can also combine the COUNTIF function with date functions like TODAY if you want to count the days until or after the current day in the data range.

In our example sheet, we would use the formula:

=COUNTIF(A2:A14,"<="&TODAY())

to count the dates before the current date.

Example 8 – Count All Instances where a Keyword Appears

Sometimes, you may have to deal with data that contains variations of the same name or item. For example, in the data below, there are variations of Apple as Apple, Apples, Fruit – Apple.

Here is the formula you can use to count the total number of cells with the keyword Apple:

=COUNTIF($A$2:$A$13,"*Apple*")

In the above example, the criterion used is *Apple*. An asterisk (*) is a wildcard character representing any number of characters. So *Apple* would count any cell with the text Apple in it, and it could be flanked by any number of characters on either side.

Using COUNTIFS Function in Google Sheets

The COUNTIF and Google Sheets COUNTIFS function works similarly. However, COUNTIFS allows you to select multiple criteria to check against before counting. The syntax for COUNTIFS goes as follows:

= COUNTIFS (criteria_range1, condition1,[ criteria_range2, condition2,…])

As you can see, it’s exactly the same as COUNTIF but with additional critera_range(s) and conditions. You can add as many as you’d like. Let’s take a look at an example to see how it works.

In the below example, we need to see if the people in the data set have brown eyes and brown hair and tested positive for a genetic condition.

We used a COUNTIFS formula with multiple criteria to check against each requirement to return a result of 1.

COUNTIF Formula Google Sheets Tips and Troubleshooting

• The COUNTIF formula can only use one criterion; use COUNTIFS for multiple criteria
• Text arguments aren’t case sensitive
• Don’t forget to use quotes on text strings and logical operators

Frequently Asked Questions

How Do You COUNTIF a Range of Numbers in Google Sheets

1. Type =COUNTIF( into an empty cell you wish to show the results in
2. Highlight the range you wish to count, then type a comma (,)
3. Type the criteria to be checked
4. Press “Enter“

Can You Do a COUNTIF With Multiple Criteria Google Sheets?

No, you have to use the COUNTIFS formula instead. It works the same as COUNTIF but allows for multiple criteria.

What Is the Criteria for COUNTIF?

You can picture the criteria as the IF statement you want to check against. For example, if you wanted to count how many people scored over 80 on a test, your criteria could be “>80.” It can also help Google Sheets count cells with text by using empty quotation marks.

How Do You Do a COUNTIF With Two Criteria Sheets?

You have to use COUNTIFS instead with the following syntax:

= COUNTIFS (criteria_range1, condition1,[ criteria_range2, condition2,…])

Wrapping Up

In this article, we have shown you how to use the COUNTIF Google Sheets function to count the number of values that match the criteria. We have also shown you the Google Sheets counting cells with text method using “<>” (not equal to sign within double quotes) as the criteria. You can substitute that operator for any other Google Sheets-compatible operator, too.

If you found this article useful, then check out how to count number words in Google Sheets.

Related:

• • Using VLOOKUP Function in Google Sheets
  • Using COUNTIF Function in Excel
  • Creating a Drop Down List in Google Sheets
  • Using IFS Function in Google Sheets
  • Using IFERROR Function in Google Sheets
  • Using OR Function in Google Sheets