COUNTIF is one of the many functions in Google Sheets for counting operations. It is the simplest function for conditional counting, which means counting the number of cells that meet specific criteria.
The purpose of the COUNTIF function is to count the number of times a particular value or pattern appears in a range of cells. To use COUNTIF, you need to specify two arguments: the range of cells you want to count and the criteria you want to use. The criteria can be a value, a text string, or a formula.
COUNTIF can be used with multiple conditions. For example, you could use COUNTIF to count the number of cells in a range that contain “available” or “in stock”.
COUNTIF can also be used in custom formula fields in conditional formatting. This means that you can use COUNTIF to create rules that automatically format cells based on their values.
In this COUNTIF A-Z tutorial, we will cover everything you need to know about using this powerful function. We will discuss the syntax of COUNTIF, how to use it with multiple criteria, and how to use it in custom formula fields in conditional formatting.
Syntax:
COUNTIF(range, criterion)To start, type =COUNTIF( in any cell, such as cell A1. Google Sheets will show you the syntax of this function in a small window, with a drop-down. Click on the drop-down to get a quick overview of the function.
range: The range of cells (data range) that are tested against the criterion. For example, A1:A, A1:Z1, A1:A1000, A1:Z1000, etc.
criterion: The pattern or test to apply to the range. For example, "apple", 100, ">"&200, DATE(2023,12,25), "Joh*", etc.
Dos and Don’ts
There are certain things to keep in mind when specifying a criterion in the COUNTIF function in Google Sheets. What are they?
If the range contains text values, use a text criterion, which must be enclosed in double-quotes.
The COUNTIF function in Google Sheets supports the use of wildcard characters to match partial strings. The wildcard characters are the asterisk (*), question mark (?), and tilde (~). You can use them to perform a partial match with a text criterion.
What about when a range contains other values?
If a selected range contains other values, such as numbers, dates, times, or timestamps, the criterion must be of the same type as the values in the range. For example, if the range contains numbers, the criterion must be a number.
However, when you use a comparison operator, you must specify the criterion as a text string, regardless of whether the value being compared is a number, date, time, or timestamp.
We will learn the proper use of criteria in COUNTIF with the examples below.
Using COUNTIF with Text
In an employee attendance sheet, the letters P, A, and H represent present, absent, and holiday, respectively. If the range is A6:G8, you can use the following formula to count the total number of Ps:
=COUNTIF(A6:G8,"P")This formula will count the number of cells in the range A6:G8 that contain the letter “P.” You can enter this formula in any blank cell in the sheet.
To count the number of absent employees, you can replace the letter “P” with the letter “A” in the formula. For example, the following formula will count the number of absent employees:
=COUNTIF(A6:G8,"A")If you don’t want to edit the formula each time, you can enter the condition in a separate cell and then refer to that cell in the formula. For example, you could enter the letter “P” in cell D2 and then use the following formula:
=COUNTIF(A6:G8,D2)This formula will count the number of cells in the range A6:G8 that contain the letter “P” in cell D2.
You can use any particular row or column in a data set as the COUNTIF range. For example, to find the total number of employees who were present on Thursday, use the following formula:
=COUNTIF(E5:E7,"P")All the above formulas use an exact match of the criterion. However, partial matches can be used in conditional count operations.
Wildcard Characters with Text Criterion in COUNTIF
The COUNTIF function in Google Sheets supports three wildcard characters for a partial match of the criterion: an asterisk (*), a question mark (?), and a tilde (~). Here are their roles in a formula:
| Wildcard Character | Purpose |
* (asterisk) | Represents zero or more characters. |
? (question mark) | Represents a single character. |
~ (tilde) followed by ?, *, or ~ | Identifies a wildcard character. |
You can use any of the three wildcard characters when using COUNTIF with text criterion in Google Sheets. Here are a couple of examples.
To count cells that begin with, end with, or contain a specific text, you can use the COUNTIF function with text criterion and wildcards in a spreadsheet.
Asterisk
The following formula counts cell values in the range A2:A that begin with the text string ID.
=COUNTIF(A2:A,"ID*")To count cell values that end with the text ID, you can use the following formula.
=COUNTIF(A2:A,"*ID")To count cell values that contain the text ID, you can use the following COUNTIF formula.
=COUNTIF(A2:A,"*ID*")All of the above formulas will match the text, even if it is just ID, because the asterisk wildcard represents zero or more characters.
Question Mark
What is the role of the question mark wildcard in COUNTIF in Google Sheets?
The question mark wildcard (?) in COUNTIF can be used to match any single character in a text criterion. For example, the criterion "P??" would match any three-letter text that begins with P and has two other characters. The two question marks represent any two characters.
You can use the following formula to count three character-length words in the range A2:A.
=COUNTIF(A2:A,"???")Tilde
This is the most complex one out of the three.
When you have any of the wildcard characters in the criterion and want the COUNTIF formula to treat it as a regular character, you can place a tilde (~) before it.
For example, see the COUNTIF formula below.
=COUNTIF(A2:A,"Joining?")
The formula is returning an incorrect value. The total number of cells matching the text string “Joining?” is 2, but the formula is returning 3.
If you want to exactly match the word Joining?, use the criterion "Joining~?". The result will be 2 instead of 3 because the tilde (~) tells the formula to treat the question mark (?) as a regular character, not a wildcard.
Using COUNTIF with Number, Date, Time, or Timestamp
The usage of date, time, and datetime (timestamp) criterion is similar in COUNTIF in Google Sheets. You can use them with or without comparison operators.
For example, let’s assume that you accept purchase orders via phone calls and the call records contain a timestamp column, a date column, and a quantity column.
Let’s see how to apply different COUNTIF criteria to this data.
Counting Cells Based on a Date
To count the total number of calls on a specific date, for example, 06/07/2023 (DD/MM/YYYY), you can use the following COUNTIF formula.
=COUNTIF(B2:B15,DATE(2023,7,6))
The COUNTIF function in Google Sheets accepts date criteria in either text format or date format.
An example of a date in text format is “06/07/2023”. However, this may cause invalid results due to the ambiguity of MM/DD/YYYY or DD/MM/YYYY formatting. Therefore, it is best to specify the date using the DATE function, whose syntax is as follows.
DATE(year, month, day)To count the number of calls made on or after 01/06/2023, use the following formula.
=COUNTIF(B2:B15,">="&DATE(2023,6,1))You must place comparison operators within double quotation marks and use the ampersand (&) to join it with the date. It makes the criterion a text string. Please scroll up and once again go through “Dos and Don’ts.”
The COUNTIF function supports the following comparison operators with date, time, timestamp, and number criteria:
>(greater than)<(less than)>= (greater than or equal to)<=(less than or equal to)<>(not equal to)
To count the number of calls made on the current date, use the TODAY() function in the COUNTIF criterion.
=COUNTIF(B2:B15,TODAY())Counting Cells Based on Time
To use time as a criterion in COUNTIF, use the TIME function.
Syntax: TIME(hour, minute, second)
Of course, you can use time as a text, such as “10:00:00”. But I prefer the TIME function because it is more precise.
I don’t have a time column in the above sample data. However, if you have time in (A) and time out (B) columns, and you want to find the number of employees who punched in before 9:30, you can use the following COUNTIF formula:
=COUNTIF(A2:A,"<"&TIME(9,30,0))How to Use DateTime (Timestamp) as a Criterion in COUNTIF in Google Sheets?
From the above examples, you can learn how to use date and time separately. When you want to use them together, i.e., as a timestamp, concatenate the date and time in the criterion.
Here is an example of how to use the timestamp criterion in COUNTIF.
=COUNTIF(A2:A15,">="&DATE(2023,7,6)+TIME(10,0,0))Counting Cells Based on Numeric Values
To count the number of cells in C2:C that contain zero numbers, we can use the following formula:
=COUNTIF(C2:C,0)Replace 0 with ">"&0 to count the values greater than zero, and "<>"&2 to count values not equal to 2.
To count non-blank cells using COUNTIF in Google Sheets, you can use the following formula:
=COUNTIF(C2:C,"<>")Replace C2:C with the range you want.
When using date, time, timestamp, or number criteria as cell references, you can enter the date, time, timestamp, or number in a cell and refer to that cell in the formula as the criterion.
If you want to use a comparison operator, do not include that in the cell. Hardcode it within the COUNTIF formula.
For example, if cell F1 contains the date 25/06/2020, and you want to count the number of cells in A1:E1000 with dates greater than 25/06/2020, the formula would be as follows:
=COUNTIF(A2:E,">"&F1)Does COUNTIF Work with Custom Number Formats in Google Sheets?
In most cases, it won’t work. However, when you format a number to text using the menu Format > Number > Custom number format, it does work.
For example, you can display a text with a number in a cell and keep the underlying value in number format.
In the following example, I have selected the cell range B3:B5 and applied the custom number format 0.00" mg" from the Format menu > Number > Custom number format. For the cell range B6:B8, I applied the 0.00" ltr" custom number format.
How do I count how many values are there in the “ltr” unit in the range B3:B8?
The following COUNTIF formula with a wildcard will return 0 because the underlying values are pure numbers, not numbers with text.
=COUNTIF(B3:B8,"*ltr")When using COUNTIF with such custom number formats, you should convert the values in the range to plain text. You can do this with the TO_TEXT function.
=ARRAYFORMULA(COUNTIF(TO_TEXT(B3:B8),"*ltr"))The TO_TEXT function requires the ARRAYFORMULA function because it cannot work with an array on its own.
COUNTIF with Multiple Conditions in Google Sheets
We usually use COUNTIFS instead of COUNTIF when we want to count a range based on multiple criteria. Both functions can accept multiple criteria.
Then, how should I know which one to choose?
When you want to test one criterion in one column, another criterion in another column, and so on, use COUNTIFS. This applies to rows as well.
When you want to test more than one condition in one column or row (range), use COUNTIF.
Here are examples of using COUNTIF with multiple conditions in Google Sheets.
=ARRAYFORMULA(COUNTIF(B2:B9,VSTACK(D2,D3)))
The above formula counts the number of times “apple” and “orange” appear in the range B2:B9 and returns the counts vertically.
What are the differences between this COUNTIF formula and the regular COUNTIF formula?
- It takes two criteria and returns two values.
- The criteria are specified within the VSTACK function.
- The ARRAYFORMULA function is used.
In short, when using the COUNTIF function in Google Sheets with multiple conditions, specify the criteria within VSTACK to get the result vertically or HSTACK to get the result horizontally.
Note: The VSTACK or HSTACK functions are not necessary when the criteria are cell references. They are useful with hard-coded criteria. So, you can rewrite the above formula with this one: =ARRAYFORMULA(COUNTIF(B2:B9,D2:D3))
The COUNTIF function in Google Sheets can only return one value. To return multiple values, we must use the ARRAYFORMULA function.
If you want the COUNTIF function with multiple criteria to return one value, wrap the formula with the SUM function.
=SUM(ARRAYFORMULA(COUNTIF(B2:B9,VSTACK(D2,D3))))The above approach may not work when we use comparison operators.
Then, what’s the solution to COUNTIF with two values?
For example, to get the number of cells in the range that contain dates between 03/07/2022 and 06/07/2022 we can use the following formula.
=COUNTIF(ISBETWEEN(A2:A11,C2,C3),TRUE)
The ISBETWEEN function returns TRUE if the first argument (A2:A11) is within the range of values defined by the second (C2) and third (C3) argument, FALSE otherwise. So the criterion is TRUE in COUNTIF.
Here is a similar topic from me: COUNTIFS with ISBETWEEN in Google Sheets.
Using COUNTIF with Advanced Functions
There are several advanced functions in Google Sheets. Examples include SORTN, QUERY, FILTER, SPARKLINE, LAMBDA, IMPORTRANGE, etc.
In advanced operations, the COUNTIF function is most useful when using it with LAMBDA helper functions such as BYROW and BYCOL.
Because these functions can expand or spill the COUNTIF results horizontally or vertically. Let’s see how to use COUNTIF in Google Sheets with BYROW and BYCOL functions.
In the following example, to get the “Day 1” count, I have used the following formula in cell F3.
=COUNTIF(B3:E3,"x")
And the following formula returns the “Week 1” count of “x” in cell B10.
=COUNTIF(B3:B9,"x")We usually copy and paste formulas to the next cell to get the count of the next row or column. For example, we can copy and paste the formula in cell B10 to cell C10 to get the count of “Week 2.”
We can spill the formulas down using the BYROW function and spill the formulas across using the BYCOL function.
To expand the formula in cell B10 across the row, use the below BYCOL function in cell B10.
=BYCOL(B3:E9,LAMBDA(val,COUNTIF(val,"x")))To expand the F3 formula down, use the following BYROW formula in cell F3:
=BYROW(B3:E9,LAMBDA(val,COUNTIF(val,"x")))When you modify this formula, you just need to change the range B3:E9. No other changes are necessary.
The above are two simple examples of using the COUNTIF function with advanced and modern functions in Google Sheets.
COUNTIF in Conditional Formatting in Google Sheets
The COUNTIF function is the backbone of the custom formula rule that highlights duplicate values in a range.
Here is how to create a custom rule using COUNTIF in Google Sheets to highlight duplicate values (text, number, date, time, or timestamp) in a range:
For example, the range to highlight is B3:B100. The following formula will return the count of B3 in the range B3:B100.
=COUNTIF($B$3:$B$100,B3)The dollar sign on both ends of the column letter in the range ensures that the range will remain the same when we copy this formula down.
The dollar sign is used to create an absolute reference, which means that the cell reference will not change when the formula is copied or moved.
To find duplicates, we must test whether the count returns a value that is greater than 1. The following formula ensures that.
=COUNTIF($B$3:$B$100,B3)>1We do not need to apply this test to each row in the range. In conditional formatting, the criterion will automatically adjust to each row in the “Apply to range.”
Countif from Another Sheet
If the range to count is in “Sheet1” and you want the formula in “Sheet2,” follow the following steps:
- Select the cell in “Sheet2” where you want to put the COUNTIF formula.
- Type the following formula:
=COUNTIF('Sheet1'!range, criteria) - Replace range with the range to count and criteria with the criterion.
What about COUNTIF from multiple sheets in a workbook?
We can use VSTACK to append the ranges vertically and use that. The following example demonstrates the same.
=COUNTIF(VSTACK(Sheet1!A1:A10,Sheet2!A1:A10,Sheet3!A1:A10),"apple")This formula will first use VSTACK to append the ranges, I mean combine additional ranges, from “Sheet1,” “Sheet2,” and “Sheet3” vertically. Then, it will use COUNTIF to count the number of cells in the resulting range that meet the criteria.
Conclusion
In this COUNTIF A-Z, I have covered everything that one needs to master this conditional count function. If you master this, you can easily learn SUMIF, SUMIFS, and COUNTIFS functions.
The main difference between SUMIF and COUNTIF is that SUMIF sums the values in a range of cells that meet certain criteria, while COUNTIF counts the number of cells in a range of cells that meet certain criteria.
The key difference is that SUMIF can take an additional sum column, while COUNTIF does not.
SUMIF Syntax: SUMIF(range, criterion, [sum_range])
Resources:
- How to Perform a Case-Sensitive COUNTIF in Google Sheets.
- How to Use COUNTIF with UNIQUE in Google Sheets.
- Countif in an Array in Google Sheets Using Vlookup and Query Combo.
- How to Count If Not Blank in Google Sheets [Tips and Tricks].
- COUNTIF to Count by Month in a Date Range in Google Sheets.
- Countif | Countifs Excluding Hidden Rows in Google Sheets.
- Countif Across Columns Row by Row – Array Formula in Google Sheets.
- How To Use Countif or Countifs In Merged Cells In Google Sheets.
