COUNTIF Excel Example (22 Examples)

Here’s a sample dataset that will be used to demonstrate how to use the COUNTIF function.

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.

The syntax of the COUNTIF function is:

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

range – put the range to define one or more cells to count, such as A1:A10.

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

Example 1 – COUNTIF Formula for Text 

We’ll go for the exact match of the given text.

• Select a cell where you want to keep your resulting value. We selected the H4 cell.
• Insert the following formula in the selected cell or into the Formula Bar.

We selected the cell range B4:B13 as the range. For criteria, we put the cell reference H3 that contains the text Adam Smith (You can use the text directly in the formula or you can use a cell reference to specify this value. To use the text directly, use the double quote (“”) to enclose the text).
The COUNTIF function will count how many times the selected text value exists in the selected cell range.

• Hit Enter.

Read More: COUNTIF Excel Example (22 Examples)

Example 2 – COUNTIF Formula for Numbers

We’ll get the exact match of the given numbers.

• Insert the following formula in the cell where you want the result, then hit Enter. We chose cell H4.

We selected the cell range D4:D13 as range. For criteria, we used the reference H3 that contains the number 23,456.

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

Example 3 – COUNTIF Formulas with Wildcard Characters

• Insert the following formula in the cell where you want the result, then hit Enter. We used the cell H4.

We selected the cell range B4:B13 as range. As criteria, we used the partial text Scott and used the wildcard character (*) around it. This means that we’ll accept any number of characters before and after the text.

Read More: COUNTIF Multiple Ranges Same Criteria in Excel

Example 4 – Count Cells Beginning with Certain Characters

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

• Insert the following formula in the cell where you want the result, then hit Enter. We chose cell H4.

The cell reference H3 contains the characters Ad*. The wildcard character (*) is at the end, so the formula checks for Ad at the beginning.

Example 5 – Count Cells Ending with Certain Characters

• Insert the following formula in the cell where you want the result, then hit Enter.

With the wildcard character (*) in the first position and an h to follow, the COUNTIF function will count the text if it ends with the character h.

Example 6 – COUNTIF for Non-Blank Cells 

• Insert the following formula in the cell where you want the result, then hit Enter.

The criteria “<>”&”” means not equal blank. The “” at the end signifies a blank value.

Example 7 – COUNTIF Blank Example

• Insert the following formula in the cell where you want the result, then hit Enter.

Criteria “” means implicitly checks whether the cell is blank.

Example 8 – COUNTIF Greater Than (>)

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

• Insert the following formula in the cell where you want the result, then hit Enter. We chose H4.

• H3 has a value of 23,456, so you’ll get a count of values greater than 23,456.

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

Example 9 – COUNTIF Less Than (<)

• Insert the following formula in the cell where you want the result, then hit Enter.

• You’ll get the count of values less than 87,045.

Example 10 – COUNTIF Equal To (=)

• Insert the following formula in the cell where you want the result, then hit Enter.

• You’ll get the count of the values equal to 87,045 (value of H3).

All logical operators also work for Dates.

Example 11 – Count Dates with Today (Current Date)

• Insert the following formula in the cell where you want the result, then hit Enter.

TODAY() returns today’s datevalue.

• You’ll get the count of today’s date throughout the dataset.

Read More: COUNTIF Date Is within 7 Days

Example 12 – Count Dates Less Than Today

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

• Insert the following formula in the cell where you want the result, then hit Enter.

• You’ll get the count of dates before today’s date.

Example 13 – Using COUNTIF to Count Dates Greater Than Today

• Insert the following formula in the cell where you want the result, then hit Enter.

• 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

Example 14 – Count Dates that Are Due in a Week

• Insert the following formula in the cell where you want the result, then hit Enter.

TODAY() gets the today’s date, adding 7 to that gives the date in a week, and then appending “=” to the start changes the condition to a check for the exact date.

• You’ll get the count of the upcoming week dates.

Read More: COUNTIF Between Two Dates in Excel

Example 15 – Using COUNTIF to Count Dates Within a Range 

• Insert the following formula in the cell where you want the result, then hit Enter.

We used the COUNTIF function twice.
In the first COUNTIF function, the criteria used is >=12/20/2021.
In the second COUNTIF function, the criteria used is >12/24/2021.
We then subtract those values to count the dates that exist in between the given range.

• You’ll get the count of dates that fall in the specified range.

Example 16 – Count Numbers within a Range

• Insert the following formula in the cell where you want the result, then hit Enter.

Both COUNTIF functions check how many numbers are greater than a specific value. By subtracting the counts, we get a count of numbers between those values.

• You’ll get the count of numbers within the range.

Example 17 – Multiple COUNTIFs to Count Cells with Multiple OR Criteria

• Insert the following formula in the cell where you want the result, then hit Enter.

Since both COUNTIF functions are checking in the same range, and we use the plus operator to sum the counts, a single value that satisfies both criteria will get counted twice. This is impossible in the sample (since one of the checks is exact and the other is partial for different values), but it’s something to keep in mind.

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

Example 18 – Find Duplicate and Unique Values in One column

• Insert the following formula in the cell where you want the result for duplicate value checks, then hit Enter.

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

• Use the Fill Handle to AutoFit the formula in the rest of the cells.

• Insert the following formula in the cell where you want the result for the unique value count, then hit Enter.

• You’ll get the count of all duplicate values based on the check for value FALSE in column G.

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

Example 19 – Using COUNTIF and SUMPRODUCT to Count Duplicate Values in a Row

We transposed the values of the dataset. Here is the new dataset.

• Insert the following formula in the cell where you want the result, then hit Enter.

• The COUNTIF function will show the number of duplicate values in the selected cell range.

Example 20 – Count Unique Values in a Row

• Insert the following formula in the cell where you want the result, then hit Enter.

• This COUNTIF function will show all the unique values in the selected cell range.

Example 21 – Using COUNTIF and SUMPRODUCT to Count Duplicates Between Two Columns

We have a dataset of two Name lists.

• Insert the following formula in the cell where you want the result, then hit Enter.

The COUNTIF function checks whether each cell in column C is not present in column B, then SUMPRODUCT multiplies that by checking if that cell isn’t blank. SUMPRODUCT loops through the entire column C this way.

• You’ll get the count of all duplicate values.

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

We used a new dataset given below.

• Insert the following formula in the cell where you want the result, then hit Enter.

In the first COUNTIF function, we selected the cell range D4:D13 as the range and used the cell reference of the 23456 number for criteria.
In the second COUNTIF function, we selected the cell range E4:E13 as the range, then used I4 as the cell reference of the 87640 number as the condition.
We added both COUNTIF functions to get the count of the used criteria in the selected cell.

• Here’s the result.

Practice Section

We’ve provided a practice sheet in the workbook to practice using COUNTIF.

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