COUNTIF Excel Example (22 Examples)

One of the most useful functions of Microsoft Excel is the COUNTIF function. You can use the COUNTIF function to count cells with specific conditions. In this article, I am going to explain a couple of COUNTIF Excel example(s).

Download to Practice

22 Examples of COUNTIF Excel 

The COUNTIF function is an Excel function which counts cells in a particular range that meet a single criterion/condition. By using the COUNTIF function you can count cells that contain

➤Dates
➤Numbers
➤Text 

The COUNTIF function also supports logical operators (>,<,<>,=) as criteria and you also can use the wildcards (*,?) for partial matching.

Let’s see the syntax of the COUNTIF function,

COUNTIF(range, criteria)
This function has two arguments these are range and criteria.

range
➤Here, you can put the range to define one or more cells to count. Such as A1:A10.

criteria
➤It is the condition based on which you want to count the range. It defines the condition that tells the function of which cells to count. You use a number, text string, cell reference or expression, etc.

1. COUNTIF Formula for Text Example 

Let’s see an example of using the COUNTIF function for text strings. Here, I’ll go for the exact match of the given text.

First, select a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range B4:B13 as the range then as criteria are given the cell reference H3 of text Adam Smith (You can use the text directly in the formula or you can use a cell reference to specify this value but to use the text directly you will need to use the double quote (“”) to enclose the text).
Now, the COUNTIF function will count how many times the selected text value exists in the selected cell range.

Press the ENTER key. Thus, you’ll get the count of the text Adam Smith.

Read More: COUNTIF Excel Example (22 Examples)

2. COUNTIF Formula for Numbers Example 

Let’s see an example of using the COUNTIF function for numbers. Here, I’ll go for the exact match of the given numbers.

First, select a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range D4:D13 as range then as criteria given the cell reference H3 of the number 23,456 (You can use the number directly in the formula or you can use a cell reference to specify this value).
Now, the COUNTIF function will count how many times the selected number exists in the selected cell range.

In the end, press the ENTER key. Thus, you’ll get the count of the number 23,456.

Read More: How to Use COUNTIF Between Two Numbers (4 Methods)

3. COUNTIF Formulas with Wildcard Characters Example

You can use the COUNTIF function with the wildcard characters to get the partial match of the given value.

First, select a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range B4:B13 as range then as criteria used partial characters Scott of the text Scott Murdock but remember to use any wildcard characters. Here, I used the wildcard character (*).
Now, the COUNTIF function will count how many times the selected text exists in the selected cell range.

Finally, press the ENTER key. Thus, you’ll get the count of the selected text.

Read More: COUNTIF Multiple Ranges Same Criteria in Excel

4. Count Cells Beginning with Certain Characters

The wildcard characters allow us to count cells beginning with certain characters while using the COUNTIF function.

First, select a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range B4:B13 as range then as criteria used cell reference H3 of the beginning characters Ad*. Here, I used the wildcard character (*) in the end position.
Now, the COUNTIF function will count the text where it started with the characters Ad.

Press the ENTER key. Hence, you’ll get the count of the text that begins with the character Ad.

5. Count Cells Ending with Certain Characters

While using the wildcards you can count not only the cells beginning with certain characters but also the cells with ending characters.

Let’s demonstrate the procedure to count cells ending with certain characters while using the COUNTIF function.

First begin with, selecting a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range B4:B13 as range then as criteria used cell reference H3 of the ending characters h. Here, I used the wildcard character (*) in the first position.
Now, the COUNTIF function will count the text where it ends with the character h.

Press the ENTER key. Therefore, you’ll get the count of the text that ends with the character h.

6. COUNTIF for Non-Blank Cells Example

You can use the COUNTIF function to count non-blank cells in a selected range.

To demonstrate to you the procedure of how you can use the COUNTIF function to count non-blank cells I purposely blanked some cells.

First, select a cell where you want to keep your resultant value.
➤ I selected the G4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range E4:E13 as range then as criteria used “<>”&” that means cells not equal blank.
Now, the COUNTIF function will count the non blank cells.

In the end, press the ENTER key. Now, you’ll get the total count of the non blank cell.

7. COUNTIF Blank Example

You can use the COUNTIF function to count blank cells in a selected range.

Let’s demonstrate the procedure to you.

First begin with, selecting a cell where you want to keep your resultant value.
➤ I selected the G4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range E4:E13 as range then as criteria used “” that means cells which are blank.
Now, the COUNTIF function will count the blank cells.

Press the ENTER key. Thus, you’ll get the total count of the blank cell.

8. COUNTIF Greater Than (>) Example

You can use the logical operators in the COUNTIF function to get values greater than, less than, not equal, equal.

Here, I will demonstrate to you the procedure of using the greater than operator in the COUNTIF function.

First, select a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range D4:D13 as the range then as criteria given the cell reference H3 of the number 23,456 then used the greater than (>) operator to search all the values greater than the given value to count.
Now, the COUNTIF function will count how many values are greater than the selected number in the selected cell range.

Press the ENTER key. As a result, you’ll get a count of values greater than 23,456.

Read More: COUNTIF Greater Than and Less Than [with Free Template]

9. COUNTIF Less Than (<) Example

Here, I will demonstrate to you the procedure of using the Less Than operator in the COUNTIF function.

First begin with, selecting a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range D4:D13 as the range then as criteria given the cell reference H3 of the number 87,045 then used the less than (<) operator to search all the values less than the given value to count.
Now, the COUNTIF function will count how many values are less than the selected number in the selected cell range.

Press the ENTER key. As a result, you’ll get the count of values less than 87,045.

10. COUNTIF Equal To (=) Example

Here, I will demonstrate to you the procedure of using the Equal To operator in the COUNTIF function.

First begin with, selecting a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range D4:D13 as the range then as criteria given the cell reference H3 of the number 87,045 then used the equal to (=) operator to search all the values equal to the given value to count.
Now, the COUNTIF function will count how many values are equal to the selected number in the selected cell range.

Press the ENTER key. Thus, you’ll get the count of the values equal to 87,045.

Remember: All logical operators work for Dates.

11. Count Dates with Today (Current Date) Example

Along with the logical operators, you can use the TODAY function in the COUNTIF function to count cells based on the current date.
Now, I will demonstrate to you the procedure of using the TODAY function with the logical operator in the COUNTIF function.

First, select a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range E4:E13 as the range then as criteria used the TODAY function to count the current date.
Now, the COUNTIF function will count how many current dates exist in the selected cell range.

Finally, press the ENTER key. Hence, you’ll get the count of today’s date.

Read More: COUNTIF Date Is within 7 Days

12. Count Dates Less Than Today

You can count the past dates from TODAY using the less than (<) in the COUNTIF function.

Now, I will demonstrate to you the procedure of using the TODAY function with the logical operator less than (<) in the COUNTIF function.

First, select a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range E4:E13 as the range then as criteria used the TODAY function along with the less than (<) operator to count the past dates from today.

Now, the COUNTIF function will count how many dates exist in the selected cell range that are less than today’s date.

Finally, press the ENTER key. Hence, you’ll get the count of the past dates of today’s date.

13. Using COUNTIF to Count Dates Greater Than Today

You can count the upcoming dates from TODAY using the greater than(>) in the COUNTIF function.
Now, I will demonstrate to you the procedure of using the TODAY function with the logical operator greater than(>) in the COUNTIF function.

First, select a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range E4:E13 as the range then as criteria used the TODAY function along with the greater than (>) operator to count the upcoming dates from today.
Now, the COUNTIF function will count how many dates exist in the selected cell range that is greater than today’s date.

Press the ENTER key. Thus, you’ll get the count of the upcoming dates of today’s date.

Read More: How to Use COUNTIF to Count Cells Greater Than 0 in Excel

14. Count Dates that are Due in a Week

By using the equal to (=) operator with TODAY in the COUNTIF function you will get the next week.

Let’s demonstrate the procedure,

First, select a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range E4:E13 as the range then as criteria used the TODAY function along with the equal to (=) operator to count the upcoming week dates from today.
Now, the COUNTIF function will count how many upcoming week dates exist in the selected cell range.

Press the ENTER key. As a result, you’ll get the count of the upcoming week dates.

Read More: COUNTIF Between Two Dates in Excel

15. Using COUNTIF to Count Specific Date Range Example

To count dates from a specific range you can use the COUNTIF function.

Let’s demonstrate the procedure,

First, select a cell where you want to keep your resultant value.
➤ I selected the H4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, I used the COUNTIF function twice.
In the first COUNTIF function, I selected the cell range E4:E13 as the range then as criteria used >=12/20/2021 date.
In the first COUNTIF function, I selected the cell range E4:E13 as the range then as criteria used >12/24/2021 date.
Now, used subtract to count the dates that exist in between the given range.

Press the ENTER key. As a result, you’ll get the count of a specific date range from the selected range.

16. Count Numbers within a Range

Just like a specific date range, you can count numbers within a specific range by using the COUNTIF function.

Let’s demonstrate the procedure,

First, select a cell where you want to keep your resultant value.
➤ I selected the H5 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, I used multiple COUNTIF functions.
In the first COUNTIF function, I selected the cell range D4:D13 as the range then as criteria used >20000 number.
In the first COUNTIF function, I selected the cell range D4:D13 as the range then as criteria used >=156789 number.
Now, used subtract to count the numbers that exist in between the given range.

In the end, press the ENTER key.
As a result, you’ll get the count of a number within a range from the selected range.

17. Multiple COUNTIF to Count Cells with Multiple OR Criteria

To count cells with multiple or criteria using multiple COUNTIF functions.

Let’s demonstrate the procedure,

First begin with, selecting a cell where you want to keep your resultant value.
➤ I selected the H5 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, I used multiple COUNTIF functions.
In the first COUNTIF function, I selected the cell range B4:B13 as the range then as criteria used the cell reference G4 of text Adam.
In the first COUNTIF function, I selected the cell range B4:B13 as the range then as criteria used the cell reference H4 of text *son (used wildcard).
Now, add both COUNTIF functions to get the count of selected criteria.

Press the ENTER key. As a result, you’ll get the count if any of the criteria are fulfilled.

Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria

18. Find Duplicates and Unique Values in One column

You can find duplicates and unique values in one column using the COUNTIF function.

Let’s demonstrate the procedure,

First, select a cell where you want to keep your resultant value.
➤ I selected the G4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range B4:B13 as range then as criteria given the cell reference B4 of text Adam Smith. Finally, used the greater than (>) 1 to find out all duplicate values denoting TRUE and FALSE. (TRUE means occurring more than one, FALSE means occurring ones as it looks for the next value)

Now, the COUNTIF function will show TRUE for duplicate values and FALSE for unique values in the selected cell range.

➤ Now, you use the Fill Handle to AutoFit the formula in the rest of the cells.

Again, select a cell where you want to keep your resultant value.

➤ I selected the I4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range G4:G13 as range then as criteria used TRUE.
Now, the COUNTIF function will show all the duplicate values in the selected cell range.

Press the ENTER key. Thus, you’ll get the count of all duplicate values.

Read More: COUNTIF between Two Cell Values in Excel (5 Examples)

19. Using COUNTIF to Count Duplicates Values in a Row

You also can count the duplicate values in a row by using the COUNTIF function with the SUMPRODUCT function.

To demonstrate to you the procedure I transposed the values of the dataset. Here is the new dataset.

First, select a cell where you want to keep your resultant value.
➤ I selected the C7 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range C3:H3 as the range then as criteria used C3:H3, next used greater than 1 to get the duplicate values as TRUE and FALSE. Then multiplied the range of C3:H3 cells where the cells are not blank. Finally, passed it in the SUMPRODUCT function as an array to show the output as numbers.

Now, the COUNTIF function will show all the duplicate values in the selected cell range.

Finally, press the ENTER key. Thus, you’ll get the count of all duplicate values.

20. Count Unique Values in a Row Example

You also can count the unique values in a row by using the COUNTIF function with the SUMPRODUCT function.

First, select a cell where you want to keep your resultant value.
➤ I selected the C7 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range C3:H3 as the range then as criteria used C3:H3, next used equal to 1 to get the unique values as TRUE and FALSE. Then multiplied the range of C3:H3 cells where the cells are not blank. Finally, passed it in the SUMPRODUCT function as an array to show the output as numbers.

Now, the COUNTIF function will show all the unique values in the selected cell range.

Press the ENTER key. Thus, you’ll get the count of all duplicate values.

21. Using COUNTIF to Count Duplicates between Two Columns Example

Between two separate lists, you can count duplicates by using the COUNTIF and the SUMPRODUCT function.

To demonstrate to you the procedure, I’ve taken a dataset of two Name lists.

First, select a cell where you want to keep your resultant value.
➤ I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, in the COUNTIF function, I selected the cell range B4:B13 as the range then as criteria used C4:C13, next used equal to 0 to get the duplicate values as TRUE and FALSE. Then multiplied the range of C4:C13 cells where the cells are not blank. Finally, passed it in the SUMPRODUCT function as an array to show the output as numbers.

Now, the COUNTIF function will show all the unique values in the selected cell range.

Press the ENTER key. Thus, you’ll get the count of all duplicate values.

22. COUNTIF on a Non-Contiguous Range of Cells Example

The COUNTIF function directly doesn’t work with the contiguous range of cells but using the combination of the COUNTIF function you can count the non-contiguous range of cells.

To explain the procedure to you, I used a new dataset given below.

First, select a cell where you want to keep your resultant value.
➤ I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

In the first COUNTIF function, I selected the cell range D4:D13 as the range then as criteria used H4 as the cell reference of the 23456 number.
In the second COUNTIF function, I selected the cell range E4:E13 as the range then as criteria used I4 as the cell reference of the 87640 number.
Then, add both COUNTIF functions to get the count of the used criteria in the selected cell.

Finally, press the ENTER key. Thus, you’ll get the count of the non-contiguous cells.

Practice Section

I’ve provided a practice sheet in the workbook to practice this explained COUNTIF example. You can download it from the above link.

Conclusion

In this article, I’ve explained 22 COUNTIF Excel examples. You can follow any of the examples of your needs. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

Related Articles

• Compare Two Tables and Highlight Differences in Excel (4 Methods)
• COUNTIF vs COUNTIFS in Excel (4 Examples)
• How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)
• VBA COUNTIF Function in Excel (6 Examples)
• How to Use COUNTIF with WEEKDAY in Excel