In daily work, counting the occurrences of specific criteria in a large Excel dataset can feel like searching for a needle in a haystack. But do not worry, Excel's COUNTIF function is here to rescue you. The COUNTIF function is a powerful tool that enables you to quickly and accurately count the number of data points that meet specific criteria. In this article, we delve into the syntax and usage of the COUNTIF function, providing eight examples to help you harness the full potential of this powerful analysis function.

Free download the sample file

Video: COUNTIF - 8 Essential Excel Examples

Subscribe our channel now: unlock top-tier Excel tips!

COUNTIF function: syntax and arguments

COUNTIF function is used to count the number of cells in a range that meet a condition.

Syntax

The generic syntax for COUNTIF looks like this:

=COUNTIF(range, criteria)

Arguments

• Range: Required. This is the range in which you want to count the specific criteria.
• Criteria: Required. This defines what you are looking for within the defined range.

A simple example for understanding the COUNTIF function

To look for a name “Judy” in a range A4:A13 and count, you should use the COUNTIF function as below:

=COUNTIF(A4:A13,"Judy")

Then press Enter key to get the count.

Here we list seven scenarios below for you to better understand how to use COUNTIF function.

COUNTIF basic usages

Count if cells with specific text (exact match)

The COUNTIF function can be used to count cells which are equal to a specific number, text or date.

Here we take the table A3:C13 as an example, column A contains fruit names, column B contains numbers, and column C contains dates. We'll separately count the occurrences of 'Apple', '100', and '6/10/2023' within these columns.

The table below provides 3 different scenarios based on above example to show how to write the criteria in the COUNTIF function:

After putting the COUNTIF function, press Enter key to get the counted result.

Count if cells contain specific text (partial match)

If you want to count cells that contain specific texts, the COUNTIF function can also help you by utilizing wildcard characters.

Wildcard characters:

• A question mark (?) matches any one character, for example, L??? finds "Lisa" and "Lucy".
• An asterisk (*) matches zero or more characters of any kind, for example, *cy finds "Lucy" and "Nancy".
• The tilde (~) is the "escape" character, putting it before a wildcard (such as ~*,~?,~*), the wildcard is then treated as a regular character, for example, ~* finds "*".

In this section, we will take counting the occurrences of the text begins with "L" in the column A3:A13 as an example.

And here, we present a list of common scenarios you may encounter based on the instance, along with the corresponding criteria that should be used in the COUNTIF function, as well as the formulas required.

After putting the COUNTIF function, press Enter key to get the counted result.

Count if cells are not blank or blank

If you have a large range of data and need to efficiently count the number of empty or non-empty cells, the COUNTIF function will be a valuable aid.

Here, we will use the range A4:A11 as an example to count the not empty cells and empty cells.

After putting the COUNTIF function, press Enter key to get the result.

Count if cells contain texts or numbers only

Sometimes, you may need to count cells that contain text strings or numbers only. In such instances, the COUNTIF function can come in quite handy.

Here, we will use the range A4:A11 as an example to count the cells that contain text only or number only.

After putting the COUNTIF function, press Enter key to get the result.

Count if numbers are greater than/less than/equal to

To count cells with values in range A4:A13 greater than, less than, or equal to a specific number, the COUNTIF function also can help you.

You can simply add the corresponding operator to the criteria. The table below illustrates the operators you can use.

Press Enter key after inputting the COUNTIF formula.

Count if dates are greater than/less than/equal to

If you need to count cells with dates that are greater than, less than, or equal to a specific date, you can use formulas similar to the ones we discussed earlier. The same principles apply to dates as they do to numbers. Here are a few examples to illustrate:

Press Enter key to get the count after putting the COUNTIF formula.

In addition to the common uses we discussed, you can leverage the COUNTIF function in combination with specific Excel Date and Time functions, such as TODAY function, to count cells based on the current date. This allows you to dynamically track and count cells that meet the criteria of the current date. Here are some examples of how you can use the COUNTIF function with TODAY():

COUNTIF advanced usages

Indeed, the Excel COUNTIF function is not specifically designed to count cells with multiple criteria. In such cases, the recommended function is COUNTIFS function. However, there are certain situations where you can achieve the desired result by combining multiple COUNTIF functions within a single formula.

>> Or criteria

Supposing you want to count the cells that equal to “Peach” or “Apple”, you should combine two COUNTIF functions together as below shown:

=COUNTIF(A4:A11,"Peach")+COUNTIF(A4:A11,"Apple")

Press Enter key to get the count.

Please note that in this scenario, we use the plus sign (+) to combine the results obtained from two COUNTIF functions. Similarly, you can create a COUNTIF formula with multiple conditions using the same approach. Here's an example of a COUNTIF formula with three OR conditions that counts occurrences of "Peach", "Apple" and "Lemon":

=COUNTIF(A2:A9,"Peach")+COUNTIF(A2:A9,"Apple")+COUNTIF(A2:A9,"Lemon")

>> And criteria

Supposing you want to count the numbers that are greater than 5 and less than 10 in a range A4:A11, you can combine two COUNTIF functions as below screenshot shown:

=COUNTIF(A4:A11,">5")-COUNTIF(A4:A11,">=10")

Press Enter key to get the count.

Please note that in this scenario, we use the minus sign (-) to combine the results obtained from two COUNTIF functions.

Count if cells are unique or duplicate values

If you want to count unique values or duplicate values in the range A3:A13, the COUNTIF function also can help you.

Step 1: Identify whether each value is unique or duplicate

1. In the column next to the values you want to count, use the formula below to identify the related value is unique or duplicate, TRUE indicates the related value is duplicate, FALSE means the value is unique.
   

   =COUNTIF($A$4:$A$13,A4)>1

2. Press Enter key and double click on the fill handle (the small green square in the lower-right corner in the first result cell), then all results are shown.
   

   

Step 2: Count unique values or duplicate values

• For counting unique values, please use below formula
  

  =COUNTIF(B4:B13,"FALSE")

• For counting duplicate values, please use below formula
  

  =COUNTIF(B4:B13,"TRUE")

  B4:B13 is the helper column you use formula in step 1.
  Then press Enter key to get the result.
  

COUNTIF –notes and some important issues

• COUNTIF function is not case-sensitive.

• COUNTIF function requires an actual range, you cannot substitute an array.

• The COUNTIF function does not support non-adjacent ranges, and its syntax does not allow specifying multiple individual cells as the first parameter. If you want to count in multiple ranges, you can use a combination of several COUNTIF functions like this:
  =COUNTIF(A2:A10,">0") + COUNTIF(B3,">0") + COUNTIF(C2:C5,">0").

• COUNTIF function returns wrong results when used to match strings longer than 255 characters.

• COUNTIF function returns a #VALUE error when referencing another workbook that is closed.