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. You can use COUNTIF to count the number of cells in a range of cells that meet a condition based on text, numerical comparisons, wildcard comparisons, dates, and more.
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 to 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 to 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 | F | |
|---|---|---|---|---|---|---|
| 1 | Employee Name | Seth Rogen | James Franco | Danny McBride | Craig Robinson | Dave Franco |
| 2 | Employee ID | 47 | 31 | 61 | 44 | 18 |
| 3 | Years of Employment | 2 | 15 | 5 | 2 | 1 |
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:F3, ">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:F1, "Craig Robinson")The function would return a value of 1.
Now suppose you want to count the number of employees whose last name is not Franco. In Excel "<>" means "not equal to," so we can combine this with wildcard matching (more on this below) and use COUNTIF to count the number of employees whose last name is not "Franco".
=COUNTIF(B1:F1, "<>*Franco")The function would return a value of 3, because three employees match the criteria of not ending in "Franco".
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:F2, 47)This formula would also return a value of 1.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product Name | Orange | Apple | Bunch of Grapes | Folger's Coffee | Coffee Beans |
| 2 | Product ID | 1237 | 1239 | 1245 | 3455 | 1475 |
| 3 | Store Cost | $0.05 | $0.06 | $0.28 | $0.98 | $1.02 |
| 4 | Retail Price | $0.88 | $0.92 | $3.00 | $6.00 | $6.99 |
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:F3, "<="&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:F2, "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:F1, "F*")This formula would return a value of 1, because only 1 product has a name that starts with the letter F.
Now say you want to use the COUNTIF function to count the total number of coffee related products. You can use wildcard matching to count the number of strings that have "Coffee" in the name.
=COUNTIF(B1:F1, "*Coffee*")This formula would return a value of 2, because there are two products that have a name that matches "*Coffee*". Notice how * can represent nothing. In Excel, the * wildcard matches a string of any length, even 0. This is why both products were counted by the COUNTIF function even though the names end and begin with the word "Coffee" with nothing before or after.
The COUNTIF function in Excel can also use dates in its criteria along with comparison operators to compare two dates. Take the following example of a grocery store which is running several coupons, each of which has an ID, an expiration date, and a discount percentage.
| A | B | C | |
|---|---|---|---|
| 1 | Coupon ID | Expiration Date | Discount % |
| 2 | 1234 | 10/8/2020 | 25% |
| 3 | 6518 | 4/20/2020 | 10% |
| 4 | 6514 | 18% | |
| 5 | 7865 | 4/18/2021 | 10% |
| 6 | 9874 | 1/1/2021 | 10% |
| 7 | 5566 | 20% | |
| 6 | 1111 | 12/15/2019 | 15% |
The manager gives you the Excel file above and asks you to determine how many coupons have expired as of the current date. You can write a formula using the TODAY function to determine how many coupons expired before today. In other words, you want to use COUNTIF to count the number of coupons which have an expiration date less than the current date.
=COUNTIF(B:B, "<"&TODAY())The formula above would tell how many coupons in the list have expired as of the current day. If the Excel file was viewed on November 18th, 2020, for example, the formula would return 3 because there are 3 expiration dates less than the current date. The TODAY function is handy because it will always update to the current date of whenever the workbook is being viewed, so your boss could open the workbook on any date in the future and see how many coupons have expired as of that date.
You may have noticed that there are also some coupons in the list with blank cells where the expiration date should be. These coupons, explains your manager, never expire. Your job is now to count the number of coupons with no expiration date. Luckily, COUNTIF can count blank cells as well! Simply use two quotes with nothing inside to indicate a blank in Excel.
=COUNTIF(B:B, "")The formula above returns 2, which means there are two coupons with no expiration date.
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.