Excel Exercises Practice Name

Excel COUNTIF Function

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.


Syntax of the COUNTIF Function

The COUNTIF Function has two required arguments (values separated by commas) and is written as follows:

=COUNTIF(range, criteria)

  • Range (required) - The range argument defines which cells are being tested against the user's criteria. EXCEL will be counting the number of cells within this range that meet the specified criteria.
  • Criteria (required) - The criteria argument is the user's criteria definition that determines which cells are counted by the function. Excel counts the number of cells which meet this criteria.



What Does the COUNTIF Function Count in Excel?

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:

  • Integers ( 1, 2, 3, 4, etc. )
  • Decimals ( 1.2, 3.45, 6.789, etc. )
  • Negative Values ( -1, -2, -3, etc. )
  • Percentages ( 100%, 200%, 35%, etc. )
  • Fractions ( 3/5, 5/3, 1 2/3, etc. )
  • Empty Cells
  • Text Values ( "Tree", "The C4TS", etc. )
  • Logical Values ( TRUE & FALSE )
  • Dates ( 3/17/2020, 'March 17, 2020', etc. )
  • Time ( 2:52, 14:52, 00:14:52:55, etc. )

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 (~?) (~*).



Examples Using the COUNTIF Function

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

Using Greater Than or Less Than with COUNTIF in Excel

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.


Matching Text with COUNTIF in Excel

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".


Matching Numbers with COUNTIF in Excel

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.



Using Cell References in the COUNTIF Function

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 "&".

Using the "?" Wildcard with the COUNTIF Function

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.

Using the "*" Wildcard with the COUNTIF Function

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.



Using Dates With the COUNTIF Function

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 B
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.


Counting Blanks With COUNTIF

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 Excel Family of COUNT Functions

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.



Excel Practice Exercises with the COUNTIF 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!