Excel COUNTIF Function and Formula with Examples


How to Use the COUNTIF Function and Formula in Excel

In this tutorial, you'll learn how to use the COUNTIF function and formula in Excel. COUNTIF is a function to count the number of cells in a range of cells that meet a single criterion, and you also can use it to count cells that contain numbers, dates, or references containing numbers.

COUNTIF Syntax

The syntax of the COUNTIF function is as follows:

=COUNTIF(range, criteria)

Arguments

  • Range: The range of cells that you want to count.
  • Criteria: The criteria used to determine which cells you want to count.

Remarks

  • Range: It can contain numbers, a named range, arrays, or references containing numbers.
  • Criteria: It can be a number, cell reference, expression, or text string.
  • COUNTIF also supports the wildcard characters? and *. You can use them in the criteria for partial matching.
  • COUNTIF ignores lower and upper case in text strings. So, for example, "Desktop" and "DESKTOP" are treated the same.

COUNTIF Excel Function Examples

The following table is a demo data that includes item codeitem namequantityunit price, and the amount we're going to use in our examples.

 ABCDEF
1Item CodeItem NameDateQtyUnit Price ($)Amount ($)
2Item01HP Desktop PC 116/3/20192210420
3Item01HP Desktop PC 117/3/201952101050
4Item02HP Desktop PC 218/3/20191250250
5Item03HP Desktop PC 318/3/201936001800
6Item01HP Desktop PC 119/3/20194210840
7Item04HP Desktop PC 419/3/20193230690
8Item01HP Desktop PC 120/3/201962101260

Example 1: Using Text Criteria

In this example, let's say you want to count the number of cells that values are equal to "HP DESKTOP PC 1" in the range A2:F2.

The COUNTIF formula will look like this:

=COUNTIF(A2:F8, "HP DESKTOP PC 1")

COUNTIF counts the number of cells that values are equal to "HP DESKTOP PC 1" in the range A2:F8 and returns 4.

Example 2: Using Logical Operator (<=) in Criteria

In this example, I show you how you use the logical operator (<=) in criteria.

For example, let's say you want to count the number of cells that the unit price is less than or equal to 250 in the range E2:E8, then you can write the COUNTIF formula as follows:

=COUNTIF(E2:E8, "<=250")

COUNTIF returns 6.

Example 3: Using Logical Operator (>) in Criteria

In this example, I show you another formula that uses the logical operator (>) in the criteria.

Let's look at the COUNTIF formula below.

=COUNTIF(E2:E8, ">700")

COUNTIF counts the number of cells that the unit price is greater than (>) 700 in the range E2:E8 and returns 0. So, there are no cells that match the specified condition.

You also can use other logical operators such as greater than or equal to (>=) and less than (<).

Example 4: Using the Wildcard Character "*" in Criteria

In this example, I show you how to use the wildcard character "*" in the criteria.
Now, let's take a look at an example:

=COUNTIF(A2:F8,"*PC 1")

In the above formula, we placed * before PC 1. Since the wildcard character (*) matches any sequence of characters in Excel, COUNTIF counts the number of cells that the item name begins with any text and ends with PC 1 in the range A2:F8. The formula returns 4.

Example 5: Using the Wildcard Character (?) in Criteria

In this example, I also show you how to use another wildcard character (?) in criteria.

To use the wildcard (?) in criteria, you can write the COUNTIF formula as follows:

=COUNTIF(A2:F8, "HP Desktop?????")

In the above formula, as you can see, there are 4 wildcard characters (?) in the criteria. 

Note that the wildcard character (?) matches any single character. 

COUNTIF counts the number of cells that the item name begins with HP Desktop and ends with the text string of 5 characters in the range A2:F8. The formula returns 7.

Example 6: COUNTIF is Not Case Sensitive

Let's take the previous formula in Example 5, and change all characters in the criteria to lower case.
Now the formula will look like this:

=COUNTIF(A2:F8, "hp desktop?????")

COUNTIF returns 7. As you can see in the sample data above, no cells are containing hp desktopCOUNTIF is not case-sensitive, so the lower and upper case characters in text string are treated the same. In this case, "HP Desktop" and "hp desktop" are considered to be the same.

If you want to count only cells containing numbers, use the COUNT function, and use COUNTA to count only cells that are not blank. If you want to count the blank cells, use COUNTBLANK, and use the COUNTIFS function to count cells that meet multiple criteria.

In this tutorial, you've learned how to use the Excel COUNTIF functionCOUNTIF is a function to count the number of cells in a range of cells that meet a single criterion. But, if you need to count the number of cells that meet multiple criteria, use the COUNTIFS function.

Leave a Reply

Your email address will not be published. Required fields are marked *