In this lesson we will learn the usage of COUNTIF function in Excel. The COUNTIF function returns a number that represents the number of cells that met a specified criteria in a specified range. Criteria can include logical operators (>,<,<>,=) and wildcards (*,?,~) for partial matching. Criteria can also include a cell reference.
=COUNTIF (range, criteria)
- range – The range of cells to be considered for counting.
- criteria – The criteria based on which the cells should be counted.
In the example below there is a list of Price which has been provided as the range(A2:A9) in first argument of COUNTIF. In cell D2 we have added the below formula.
In cell D1 the value has been specified as 71.57 and the criteria to count has been specified as “>”&D1 which means COUNTIF will count the number of cells from the range containing the value which is greater than D1(71.57). To provide the criteria with logical operator along with cell reference, we need to enclose the logical operator within quotes and the cell reference should be prefixed by &.
In the below example we have used the same range and in criteria we provided “<71” in COUNTIF function in cell C2 as follows.
It returned the count of cells containing values less than 71 which is 5.
Note here if you are using the logical operator along with a number(without cell reference) then you need to enclose both, together within quotes.
In the below example the COUNTIF function is used to count the cells in range A2:A14 which is equal to the text mentioned in cell D1 that is “mike”. Here is the formula used in cell D2.
Note in the above example the COUNTIF returned 2 here as there are 2 cells with value equal to “Mike”. Cell A13 also contains the string “Mike” but it contains the additional characters as well hence it doesn’t exactly match with the criteria. If you need to count all cells in the specified range which contains the specified text then we need to use wildcard characters in the criteria of COUNTIF function as follows. Here in cell D2 we have added the below formula.
Now we will see how can we use the COUNTIF function with dates. In below example we have a table containing the date of admission for couple of students.
Here we have intentionally added the admission dates in different format to show that COUNTIF function can work with all kind of date formats.
In cell E2 we have applied the below formula to find the number students admitted today(today’s date is 17th August, 2020)
As you can see the count of today returned by COUNTIF function in cell E2 is 2 which is correct as per the dates mentioned in cell range B2:B10 where today’s date is present twice in different formats.
We can also use logical operators with dates in the argument of COUNTIF function.
We have used the below formula in cell E3 to find the admission dates before 2020(before 1st January,2020).
As you can see below, the result is 5 since in cell range B2:B10, there are 5 dates before 2020.
Now we will see how can we find duplicate and unique entries in a list using COUNTIF function. In the below example we have a list of student’s name containing some duplicates in column A.
In Column B we used the below formula to return the boolean value TRUE/FALSE depending on the multiple occurrences of the content of each cell in the entire list A2:A14.
This is the formula in cell B2 and the same has been dragged for the below cells in column B. Here the source range is being made as constant(A$2:A$14) and the reference of the cell to validate the content will be updated as A2, A3,A4 and so on for the following cells.
Here if more than one occurrences found then it returns TRUE else returns FALSE.
Now in cell D2 we have used the below formula to get the count of unique names.
Since there are 6 occurrences of boolean value FALSE in cell range B2:B14, it means there are 6 unique names in the list of column A. With this example we have also seen how the COUNTIF function can be used with boolean values.
Now we will learn another trick with text string using COUNTIF function. In below example we are using COUNTIF function to find the count of text string in range A2:A14 with the following formula in cell C2.
Here ‘?’ means a single character. ‘?’ after the text ‘John’ means a single character following the string ‘John’. This is how it returns 1 that is there is only one occurrence of the string starting with ‘John’ plus 1 character that is ‘Johny’.
If you add two ‘?’ in our formula as follows then it will consider the string ‘Johnyy’ which starts with ‘John’ plus 2 characters.
Now we will see the use of COUNTIF function to count the blank and non blank cells.
In our below example there are some blank date of admission in column B for the list of students in column A. In cell E2 we have used the below formula to get the count of blank cells in range B2:B10.
In cell E3 we have used the below formula to get the count of non blank cells in range B2:B10.
In our last example we will show how multiple COUNTIF function can be used in mathematical operations like addition, subtraction etc. a part of formula. We will also learn to use different mathematical excel function to use as argument of COUNTIF function. In the below table we have a list of student’s age.
Here we first add a name for the column B. First select the cell range B2:B8 and then click Data in the menu bar of the Google Sheet. Then Go to Named ranges.
Now in the Named ranges pane provide the desired name for the selected cells of the column. Here we provided the name of the column as ‘Age’. Then click Done.
Now we will also learn to use the COUNTIF function with column name instead of providing the cell range as argument.
Now we need the count of the students with age less than average age of all students in addition to the students with age more than 32.
Here we have used the below formula in cell E2. The logical operators that are less than(<), greater than(>) symbols we have taken from cell reference which we could have been applied directly in the formula as well.
Here cell D3 contains less than(<) operator which we have used in first COUNTIF function in association with the AVERAGE function to get the count of the students with age less than average age of all students. Here the Average age is 30. So the count of students with age less than 30 returned as 3.
Cell D2 contains greater than(>) operator which we have used in second COUNTIF function in association with the cell reference D4 where the value 32 is stored, to get the count of students with age more than 32. The second COUNTIF function returns the count 1 as only one student is of age more than 32.
So the addition of the results of two COUNTIF functions returned as 4 in cell E2.
- From the previous examples we have already seen that the Criteria are case-insensitive (“Mike” and “mike” are treated as same).
- It can handle both integer and decimal numbers.
- COUNTIF returns incorrect result when used to match strings longer than 255 characters.
- COUNTIF will return #VALUE error when referencing another workbook that is closed.
- There are 3 wildcard characters in Excel – question mark (?), asterisk (*), and tilde (~) , all are supported by COUNTIF function
- A question mark is used to match any single character
- An asterisk is used to match any sequence of characters.
- If you need to find an actual question mark or asterisk, type a tilde (~) before the character.