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).

**Table of Contents**hide

## 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**.

`=COUNTIF(B4:B13,H3)`

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**.

`=COUNTIF(D4:D13,H3)`

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**.

`=COUNTIF(B4:B13,"*Scott*")`

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.

➤ I selected the **H4 **cell.

Then, type the following formula in the selected cell or into the **Formula Bar**.

`=COUNTIF(B4:B13,H3)`

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**.

`=COUNTIF(B4:B13,H3)`

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**.

`=COUNTIF(E4:E13,"<>"&"")`

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**.

`=COUNTIF(E4:E13,"")`

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.

➤ I selected the **H4 **cell.

Then, type the following formula in the selected cell or into the **Formula Bar**.

`=COUNTIF(D4:D13,">"&H3)`

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**.

`=COUNTIF(D4:D13,"<"&H3)`

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**.

`=COUNTIF(D4:D13,"="&H3)`

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.

➤ I selected the **H4 **cell.

Then, type the following formula in the selected cell or into the **Formula Bar**.

`=COUNTIF(E4:E13,TODAY())`

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.

➤ I selected the **H4 **cell.

Then, type the following formula in the selected cell or into the **Formula Bar**.

`=COUNTIF(E4:E13,"<"&TODAY())`

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.

➤ I selected the **H4 **cell.

Then, type the following formula in the selected cell or into the **Formula Bar**.

`=COUNTIF(E4:E13,">"&TODAY())`

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,

➤ I selected the **H4 **cell.

Then, type the following formula in the selected cell or into the **Formula Bar**.

`=COUNTIF(E4:E13,"="&TODAY()+7)`

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,

➤ I selected the **H4 **cell.

Then, type the following formula in the selected cell or into the **Formula Bar**.

`=COUNTIF(E4:E13, ">=12/20/2021")-COUNTIF(E4:E13, ">12/24/2021")`

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**.

`=COUNTIF(D4:D13,">20000")-COUNTIF(D4:D13,">=156789")`

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**.

`=COUNTIF(B4:B13,G4)+COUNTIF(B4:B13,H4)`

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**.

`=COUNTIF(B4:B13,B4)>1`

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**.

`=COUNTIF(G4:G13,TRUE)`

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**.

`=SUMPRODUCT((COUNTIF(C3:H3,C3:H3)>1)*(C3:H3<>""))`

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**.

`=SUMPRODUCT((COUNTIF(C3:H3,C3:H3)=1)*(C3:H3<>""))`

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**.

`=SUMPRODUCT((COUNTIF(B4:B13,C4:C13)=0)*(C4:C13<>""))`

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**.

`=COUNTIF(D4:D13,H4) + COUNTIF(E4:E13,I4)`

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.