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.


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.
  • Criteria (required) - The criteria argument is the user’s criteria definition that determines which cells are counted by the function.



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



Examples Using the COUNTIF Function

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.



Using Cell References in the COUNTIF Function

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

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:E2, "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:E1, "F*")

This formula would return a value of 1, because only 1 product has a name that starts with the letter F.



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!