The COUNTIF function in Microsoft Excel is used to count cells that meet a specific criterion or condition. Below is a set of examples that explain how to use the COUNTIF function in Excel.
COUNTIF function in Excel
The COUNTIF function in Excel can be used to count cells that contain numbers, dates and text that match certain criteria.
For example, given a list of people by name and age, the COUNTIF function can be used to count the number of people over the age of 18 or people with the same family name.
The COUNTIF function supports logical operators (=, <, >) and wildcards (*,?) to count cells that meet subcriteria.
COUNTIF function syntax
The syntax of the COUNTIF function is COUNTIF (range, criteria)
- Area: The range of cells to count
- Criteria: The condition that defines the cells to count.
1. Use the COUNTIF function in Excel (examples)
Now that you understand the syntax of the COUNTIF function in Excel, let’s look at some examples to understand the actual usage of the COUNTIF function.
Let’s say you have an Excel spreadsheet with you names of the people in column A and theirs age in column B
The COUNTIF function can be used to count people over 18, under 18 and people who are exactly 18 years old.
1. To count the number of people who are exactly 18 years old, enter =COUNTIF(B3:B11,18) and press Enter on your computer keyboard.
In the example above B2 to B11 is the ‘area’ of cells to count and 18 (age) is the criterion.
Tip: Instead of typing B3:B11, it is much easier to select the range B3:B11 with the mouse.
2. To count people over the age of 18, enter =COUNTIF(B3:B11”,>18″) and press Enter.
Likewise, you can use the formula =COUNTIF(B3:B11”,<18”) Counting people under the age of 18.
3. To count people named Andy, type =COUNTIF(A3:A11,”Andy”) and press Enter.
2. Reference cells in the COUNTIF function
You can use the COUNTIF function to refer to other cells that contain the values or criteria.
For example, suppose the qualifying age for a particular job is available in Cell C3 as shown below.
In this case, you can count people older than 21 by tapping =COUNTIF(B3:B11,”>” & C3) and pressing Enter.
3. Use wildcard characters in the 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
used to match any sequence of characters.
If you want to find a question mark (?) or an asterisk
you can use a tilde (~) before these wildcard characters (~? or ~*).
4. Common problems with the COUNTIF function Here are some of the problems you may encounter while using the COUNTIF function in Excel.
1. The COUNTIF function counts both uppercase and lowercase characters in text strings. For example, cells containing “ORANGES” and “ORANGES” are treated the same.
The COUNTIF function returns false or incorrect results when used to match strings longer than 255 characters. For matching strings longer than 255 characters, you can use the “&” operator or the CONCATENATE function. For example, you can type
=COUNTIF(A1:A10, “250 chars”&”remaining chars) to exceed the 255 character limit.
3. The COUNTIF function reports an error (#value!) if the formula containing the COUNTIF function refers to another worksheet that is closed. In other words, the referenced worksheet must be open.
When counting text values, make sure the data does not contain leading spaces, trailing spaces, double quotes, or non-printable characters. Related