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:
Related Functions:
IF: Allows you to check for specific conditions across a dataset
SUMIF: Use to sum numbers if they meet a certain condition..
SUMIFS: Sums data from cells that meet multiple criteria
COUNTIFS: Used for counting rows that fulfil two or more criteria
COUNT IF Not Blank: Count cells that are not empty.
IFS: Allows you to combine many if functions in one statement
IFERROR: Replaces formula error messages with specified text or a blank cell.
IF THEN: Evaluates data against a condition and take a corresponding action when the result is TRUE.
IF AND: Combining the IF and AND functions allows you to include multiple conditions that must be met to return a TRUE result.
IF ELSE: Evaluates a condition, and takes one action when the result is TRUE and a different one when the result is FALSE.
Multiple IF: Learn how to use multiple if statements in a single formula
IF OR: Evaluates multiple conditions and produces a TRUE result when any of them are met.
IF Contains: Returns cells that contain a particular text.
AVERAGEIF: Calculate an average for numbers within a data range if they meet the provided criteria.
Recommended articles
More resources
