The COUNTIF function in Google Sheets returns the number of cells from a range of cells which meet a specific criterion. For example, you can use COUNTIF to count the number of cells which contain a certain number, certain text, or which meet a specified logical condition (e.g. contain a number larger than 100). The COUNTIF function in Google Sheets only compares cells against one specific criterion. To count the number of cells which meet multiple criteria, see the COUNTIFS function.
The COUNTIF function in Google Sheets takes two arguments (function inputs inside the parenthesis separated by commas). Both arguments are required.
=COUNTIF(range, criterion)The COUNTIF function will count the number of cells in the range which meet the criterion.
To count the number of cells in a range which contain a specific number, use the COUNTIF function and simply specify the range of cells for the range argument, and supply the desired number as the criterion argument.
| A | B | |
|---|---|---|
| 1 | Account Manager | Number of Clients |
| 2 | Smith | 1 |
| 3 | Perez | 2 |
| 4 | Chan | 3 |
| 5 | Johnson | 2 |
| 6 | Pedersen | 3 |
For example, say your company has a table with account managers and the number of clients to which each is assigned. You want to count the number of account managers with 3 clients.
=COUNTIF(B2:B6, 3)In the formula above, the range is B2:B6, because we want to test each cell from B2 to B6 against the criterion. The criterion is 3, because we want the count of account managers with 3 clients. This formula would return a value of 2, because two cells in the range match the criteria - Chan and Pedersen are both assigned to 3 clients. To count the number of account managers with 2 clients, we would simply use 2 for the second argument rather than 3.
You can also use COUNTIF to count the number of cells which contain specific text in your Google Sheet. To count the number of times text appears, use the COUNTIF function and simply supply the string of text in quotes as the second argument of the formula.
| A | B | |
|---|---|---|
| 1 | Employee ID | Pet |
| 2 | 123 | Dog |
| 3 | 543 | Cat |
| 4 | 135 | Dog |
| 5 | 246 | Dog |
| 6 | 789 | Donkey |
| 7 | 111 | Dog |
Say you work in an office where employees can bring pets to work. You have a table in Google Sheets showing employees along with the type of pet each brings to the office. Say you want to count the number of employees who bring dogs to the office.
=COUNTIF(B2:B7, "Dog")The formula above takes B2:B7 as its range, because we want it to look at each cell from B2 to B7 and compare the cell against our criterion. The criterion in this formula is "Dog" because we want to count the number of cells which contain the text "Dog". This formula would return a value of 4 because there are 4 cells in the range which contain the text "Dog".
Notice that while the plain number criterion from the previous example was not in quotes, text criteria must be enclosed in quotes.
Another thing to note is that COUNTIF is not case-sensitive when evaluating text criteria. This means that a cell could contain the text "Dog" or "dog" or "dOG" and the COUNTIF function would treat these all the same way and add them to the total.
Imagine you're using text matching with the COUNTIF function and don't know the exact text you need to match against. For example, maybe you want to count all text that starts or ends with a certain letter, or all text that is a certain length of characters. You can accomplish these tasks with wildcard matching.
In Google Sheets, an asterisk (*) can replace zero or more random letters, while a question mark (?) replaces exactly one letter. For example, "A*" would match any string of text which starts with the letter A and "*A" would match any text which ends with the letter A, because the asterisk replaces any number of characters.
On the other hand "A???" would only match with text which starts with A and is exactly 4 characters in length, because the question mark only replaces one character in a string.
Building on the pet example above, we can try some wildcard matching.
=COUNTIF(B2:B7, "D*")This formula counts the number of cells in the range which start with D and are any length. It would return a value of 5, because there are four cells which contain the text "Dog" and one cell which contains the text "Donkey", each of which starts with the letter D.
=COUNTIF(B2:B7, "D??")This formula uses question marks instead of an asterisk, which means that it counts the instances of text which start with the letter D and are exactly 3 characters long. This formula would return a value of 4 because there are 4 cells which contain the text "Dog". "Donkey" also starts with D but has too many characters to be a match.
=COUNTIF(B2:B7, "???")This formula counts the instances of text which can be anything that is exactly 3 characters long. This would return a value of 5 because there are 4 cells which contain the text "Dog" and 1 cell which contains "Cat" which all meet the criterion of being 3 characters long.
=COUNTIF(B2:B7, "*")Finally, this COUNTIF formula matches with any text of any length. Recall that the asterisk can replace any number of characters while each question mark only replaces one character. This formula would return a value of 6 because there are 6 cells in the range which match the criterion.
The COUNTIF function can also use the logical operators in Google Sheets and count the number of cells which pass a logical test. For example, say you want to count all the cells which contain a number greater than 50, or less than 100.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Department | Number of Employees | Budget | 10,000 | |
| 2 | Accounting | 9 | 10,000 | ||
| 3 | Finance | 14 | 20,000 | ||
| 4 | IT | 10 | 5,000 | ||
| 5 | R&D | 16 | 8,000 | ||
| 6 | Admin | 3 | 6,000 | ||
| 7 | Marketing | 8 | 20,000 | ||
| 8 | Legal | 5 | 6,000 |
Recall the logical operators in Google Sheets:
You can supply logical expressions enclosed in quotes as the second argument of the COUNTIF function.
Say, for example, that you want to count the number of departments in your company with fewer than 6 employees.
=COUNTIF(B2:B8, "<6")The first argument is B2:B8 because we want to test each cell from B2 to B8. The second argument is "<6" (notice that it is enclosed in quotes) because we want to test whether each cell in the range contains a number less than 6. This formula would return a value of 2, because only 2 departments (Legal and Admin) have fewer than 6 employees.
Now say that you want to count the number of departments with budgets greater than or equal to 10,000.
=COUNTIF(C2:C8, ">=10000")The first argument for this formula is C2:C8, because now we're counting cells from the budget column of the table. The second argument is ">=10000" because we want the number of cells which contain a value greater than or equal to 10,000. This formula would return a value of 4 because there are 4 cells in the range which match the criterion.
Now say you want to write a COUNTIF formula with a logical comparison, and you want to compare each cell in the range against the value in another cell. For example, say the maximum recommended budget for a department is the number in cell E1.
In Google Sheets, the ampersand (&) is used to concatenate, or join, different values together. The COUNTIF function allows comparisons to values in other cells by concatenating the logical operator to the cell reference. For example, to compare whether a number is less than the number in cell E1, we would write "<"&E1.
=COUNTIF(C2:C8, "<"&E1)This COUNTIF formula counts the number of cells in the range B2:B8 which are less than the value in cell E1. The value is currently 10,000, so this formula would return a value of 3. Using this approach, one can change the value in cell E1 without changing the value in each COUNTIF formula in the Google Sheet.
The best way to really learn a new function in Google Sheets is to get some hands-on practice and commit it to memory. Try some practice problems with the COUNTIF function now!