The google sheets COUNTIF function is used to count instances that fulfill a certain condition. Read this tutorial to learn how COUNTIF function in Google Sheets is used, with several examples and common COUNTIF formula errors.

## Google Sheets COUNTIF Syntax

The COUNTIF function in Google Sheets is a combination of the COUNT and IF function. It is used to count the number of cells that meet a criterion.

The syntax is

**=COUNTIF(range, criterion)**

where

**range **

the reference to the set of cells where you want to count

**criterion**

the value to find and count across the data range. COUNTIF can accommodate the following criteria:

- An exact number
- A string, enclosed in double quotes
- Comparison expressions such as greater than or less than

The criterion can also be stored in another cell, and the reference placed instead. This allows the user to change the criterion anytime they need to without having to edit the function.

Basically, this syntax says

** =COUNTIF(Where do you want to count?, What do you want to count?)**

## How to use COUNTIF in Google Sheets

The COUNTIF function is used to count instances where a certain criterion is fulfilled within a given range in Google Sheets. Here are some use cases of the COUNTIF function:

- Tracking the number of times a certain item has been bought
- Tracking the number of times a single purchase exceeds a set amount
- Tracking the number of times where a certain amount of an item has been bought

All of these will be shown in the examples in the next section.

## COUNTIF Function Examples

We will have several examples of the COUNTIF function in action below, including screenshots of the Google Sheets:

### Count instances of values equal to a number

In this COUNTIF function example, we count the number of orders that have exactly three items. The resulting function is:

**=countif(D:D,3)**

The number does not need to be enclosed in double or single quotes and can be included as is. The result in Google Sheets is shown below:

### Count instances of an exact string appearing

In this COUNTIF function example, we count the number of times one of the products, “5 lb flour”, was ordered. The function is:

**=countif(B:B,"5 lb flour")**

The result in Google Sheets is shown below:

### Count instances of a value stored in another cell

In this COUNTIF function example, we count the number of times a product, “18-egg set” was ordered. However, we store the string in another cell, and use its reference inside it:

**=countif(B:B,K11)**

The result in Google Sheets is shown below:

### Combine COUNTIF with dropdown box

In this COUNTIF function example, we count the number of times one of the products, “loaf bread”, was ordered. We design a dropdown box and use its reference inside it:

**=countif(B:B,K8)**

The result in Google Sheets is shown below:

### Count instances of a string appearing exactly or part of another string in the sheet

If you are unsure of the exact string that you need to search in Google Sheets but are certain about words or numbers that are part of it, you can use Google Sheets wildcards. They are appended to the string to denote their relative position on the string. The two wildcard symbols in Google Sheets are the following:

Question mark symbol ? - used to denote that there is exactly a single character before or after a string

Asterisk symbol * - used to denote that there are characters before or after a string, which may vary in length

Let’s say we want to search for strings containing the word “egg”. Here are the possibilities when combining “egg” with the asterisk symbol:

***egg** - the word “egg” is located at the end of the string

**egg*** - the word “egg” is located at the start of the string

***egg*** - the word “egg” is located at the middle of the string

We can use wildcards with the COUNTIF function. For our example, we want to count the times egg products were ordered: There are three egg products in the range: “6-egg set”, “12-egg set”, and “18-egg set”. As you can see, the word “egg” is in the middle of the names. We then set the condition as

***egg***

The resulting formula will be:

**=countif(B:B,"*egg*")**

You can check it in action below, alongside other COUNTIF function defined for each type of egg product. Note that the total for individual products is equal to the result for the “*egg*” condition. The result in Google Sheets is shown below:

### Count instances of values that are greater than or less than a number

One of the strengths of the COUNTIF function is to allow defining comparisons as the input condition. Comparisons simply mean expressions that can either mean greater than or less than.

You can define one of the four comparison expressions inside the COUNTIF function:

Greater than: **“>35”**

Less than: **“<35”**

Greater than or equal to: **“>=35”**

Less than or equal to: **“<=35”**

To include a comparison expression, we treat it as a string, enclosing it in double quotes. For this exemple, we want to count all sales with profit less than 3 dollars. The formula is:

**=COUNTIF(H:H,”<3”)**

The result in Google Sheets is shown below:

In this example, we want to count all sales with profit more than 35 dollars. The formula is:

**=COUNTIF(H:H,”>=35”)**

The result is:

## Common COUNTIF Formula Errors

### Parsing error

You can get a generic error code when trying to type the function in Google Sheets. This can occur when the device you use automatically converts punctuation marks; one example is iOS devices that have Smart Punctuation feature. What you want to type is the following:

**=COUNTIF(E:E,”>=35”)**

But you get the following:

**=COUNTIF(E:E,“>=35”)**

You can see the difference below:

It all boils down to the form of double quotation marks inputted in Google Sheets. To solve this error, you should switch off any automatic punctuation converters that may be present in the device you are using, such as iOS’s Smart Punctuation feature.

### Counts entries that do not have the exact case per letter

COUNTIF is actually case-insensitive. This means that Google Sheets does not discriminate case variations to the letter, and will all count them. You can see it in the example below:

The exact string “egg” only appears thrice but COUNTIF counted 13 instances!

How can we make it case-sensitive in Google Sheets?

We can combine it with ARRAYFORMULA and EXACT or REGEXMATCH function.

If you want exact string match (with no allowance for partial matches), use EXACT function:

**=countif(ArrayFormula(EXACT(range,criterion)),TRUE)**

where

**range **is the reference to the set of cells where you want to count

**criterion** is the value to find and count across the data range

You can see this in action in the example below:

If you want partial matches such as our example using wildcards, use REGEXMATCH function:

**=countif(ArrayFormula(REGEXMATCH(range,criterion)),TRUE)**

where

**range **

the reference to the set of cells where you want to count

**criterion**

the value to find and count across the data range. For this case, there is no need to use wildcard symbols.

You can see this in action in the example below:

## Need to use a set of criteria instead of a single one?

If you need to apply a set of criteria instead of a single one, COUNTIFS is the perfect match! Head over to our COUNTIFS ultimate guide to learn more how to use it!

#### Enter Your Email Below to Try Lido for FREE!

- One Click Data Imports From Anywhere
- Transform Spreadsheets into Software in Seconds
- Pre-built & Custom Templates
- Visualize Real-Time Data in Centralized Dashboards