The COUNTIF Function in Microsoft Excel is used to count cells matching a particular criteria or condition. You will find below a number of examples explaining the use of COUNTIF Function in Excel.
COUNTIF Function in Excel
The COUNTIF Function in Excel can be used to count cells containing Numbers, Dates and text matching a specific criteria.
For example, if there is a list of people with Names and Ages, the COUNTIF function can be used to count number of people above the age of 18 or people having the same family name.
The COUNTIF Function supports logical operators (=, <, >) and wildcards (*,?) for counting cells matching partial criteria.
Syntax of COUNTIF Function
The Syntax of COUNTIF Function is COUNTIF (range, criteria)
- Range: The range of cells to count
- Criteria: The condition that defines the cells to be counted.
1. Use COUNTIF Function in Excel (Examples)
Now that you understand the Syntax of COUNTIF Function in Excel, we can take a look at some examples to understand the actual usage of COUNTIF Function.
Let us assume that you have an Excel worksheet with Names of people in Column A and their Ages in Column B.
The COUNTIF Function can be used to count people who are above 18 years, below 18 years and those who are exactly 18 years old.
1. To count number of people who are exactly 18 years of age, type =COUNTIF(B3:B11,18) and hit the enter key on the keyboard of your computer.
In above example B2 to B11 is the ‘Range’ of cells to Count and 18 (Age) is the Criteria.
Tip: Instead of typing B3:B11, it far more easier to select the range B3:B11 using the mouse.
2. To count people who are older than 18 years, type =COUNTIF(B3:B11,”>18″) and hit the enter key.
Similarly, you can use the formula =COUNTIF(B3:B11,”<18″) to count people who are younger than 18 years of age.
3. To count people who have the name “Andy”, type =COUNTIF(A3:A11,”Andy”) and hit the enter key.
2. Reference Cells in COUNTIF Function
The COUNTIF function allows you to reference other cells containing the values or the criteria.
For example, let us assume that the qualifying age for a certain activity is available in cell C3 as shown bwlow.
In this case, you can count people who are older than 21 years by typing =COUNTIF(B3:B11,”>” & C3) and pressing the enter key.
3. Use Wildcard Characters in COUNTIF Function
As mentioned above, the COUNTIF Function supports the use of wildcard Characters (? & *).
The question mark (?) is used to match a single character, while the Asterisk (*) is used to match any sequence of characters.
In case you want to find a question mark (?) or an asterisk (*), you can use a tilde (~) in front of these Wildcard characters (~? or ~*).
4. Common COUNTIF Function Problems
Here are some of the problems that you may come across while using the COUNTIF Function in Excel.
1. COUNTIF Function counts both upper and lower case letters in text strings. For example, cells containing “Oranges” and “ORANGES” will be treated as the same.
2. The COUNTIF Function returns wrong or incorrect results when used to match strings greater than 255 characters.
For matching strings greater than 255 characters, you can make use of the “&” operator or the CONCATENATE Function.
For example, you can type =COUNTIF(A1:A10, “250 characters”&”remaining characters) to get over the limit of 255 characters.
3. COUNTIF Function reports an error (#Value!) when the formula containing the COUNTIF function refers to another worksheet that is closed. In other words, the referred worksheet needs to be open.
4. While counting text values, make sure the data does not contain leading spaces, trailing spaces, quotation marks or nonprinting characters.