The COUNTIF function in Excel returns a value corresponding to the counted number of cells that meet a single specific criterion specified by the user.
The COUNTIF Function has two required arguments (values separated by commas) and is written as follows:
=COUNTIF(range, criteria)
As with most Excel functions, the COUNTIF function only has certain inputs it can comprehend and calculate. Here’s a quick sample of types of values that COUNTIF can process:
The COUNTIF function uses Excel's logical operators evaluate matches. Here’s a quick showcasing of their purposes:
Operator | Meaning | |
---|---|---|
1 | < | Less than |
2 | > | Greater than |
3 | <> | Greater than or less than (not equal) |
4 | <= | Less than or equal |
5 | >= | Greater than or equal |
The COUNTIF function also uses Excel's wildcard operators evaluate matches. Here’s a quick showcasing of their purposes:
Wildcard | Meaning | |
---|---|---|
1 | ? | Single character wildcard |
2 | * | Wildcard string of undefined length |
As seen above, the wildcard operators in Excel are a question mark (?) and an asterisk (*). If you need to use a literal question mark or asterisk in their function, place a tilde (~) before the symbol (~?) (~*).
Take the following example of an Excel table with employee details.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Employee Name | Seth Rogen | James Franco | Danny McBride | Craig Robinson |
2 | Employee ID | 47 | 31 | 61 | 44 |
3 | Years of Employment | 2 | 15 | 5 | 2 |
If one of wanted to count the number of employees with more than 2 years of experience, they would use the following function:
=COUNTIF(B3:E3, ">2")
The function would return a value of 2. Using quotations around defined criteria is necessary when using logical operators, text, and wildcards in Excel. The only time quotations are not necessary is when your criteria are plainly numerical and lack a logical operator or wildcard.
Using the above Excel table, if one wanted to find the number of employees named "Craig Robinson" they would use the following function:
=COUNTIF(B1:E1, "Craig Robinson")
The function would return a value of 1.
Using the above table, if one wanted to find the number of employees with the Employee ID of 47, they would use the following formula:
=COUNTIF(B2:E2, 47)
This formula would also return a value of 1.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Product Name | Orange | Apple | Bunch of Grapes | Folger's Coffee |
2 | Product ID | 1237 | 1239 | 1245 | 3455 |
3 | Store Cost | $0.05 | $0.06 | $0.28 | $0.98 |
4 | Retail Price | $0.88 | $0.92 | $3.00 | $6.00 |
If one wanted to find the number of products that have a store cost less than or equal to the store cost of a "Bunch of Grapes" one would use the following formula:
=COUNTIF(B3:E3, "<="&D3)
The formula would return a value of 3. When using a cell reference in the COUNTIF function, one must CONCATENATE with the symbol "&".
The "?" wildcard in Excel is used in place of a single character. For example, if one wanted to find the number of Product IDs starting with "12" with exactly four characters, they would use the following formula:
=COUNTIF(B2:E2, "12??")
This formula would return a value of 3, because 3 products have IDs that start with 12.
The "*" wildcard in Excel is used in place of any string of characters. Unlike the "?" wildcard that only matches one character, the * wildcard matches any length of characters. If one wanted to find the amount of text strings in the Product row of the above table that start with the letter F (regardless of name length), they would use the following formula:
=COUNTIF(B1:E1, "F*")
This formula would return a value of 1, because only 1 product has a name that starts with the letter F.
The COUNTIF function outlined in this tutorial is one of many belonging to the group of COUNT functions. There are many other COUNT functions used in Excel, suited for a variety of tasks.
If counting numbers only, use the COUNT function.
If counting numbers AND text, use the COUNTA function.
If counting empty cells, use the COUNTBLANK function.
If counting based on a single criterion, use the COUNTIF function.
If counting based on multiple criteria, use the COUNTIFS function.
Now that you've read about the COUNTIF function in Excel, try a few fun practice exercises to get some hands-on experience and internalize the function.
Try some Excel Exercises with the COUNTIF function now!