In MS-Excel there are several functions purposed for counting different kinds of cells, such as **blanks** or non-blanks, with a number, date, or text values, containing specific words or characters, specific time. In this article, we will discuss the various uses of **COUNTIF **between two cell values in Excel.Â

**Table of Contents**hide

## Download the Practice Workbook

**What is COUNTIF Function**

The syntax of the function is as follows:

`COUNTIF (range, criteria)`

In this function both the arguments in the parameter are mandatory. Firstly, it takes the **range** of cell which will be counted. The second section takes the **criteria** which is the condition. Based on this condition the counting will be executed. For more details visit this pageÂ **Link**

**5 Ways to use COUNTIF to Compare Two Cell Values in Excel**

**1. How to Count Cells by Comparing Numbers and Dates Using COUNTIF**

Here I will show how to compare cells using the **COUNTIF** function. Letâ€™s assume we have a dataset of some **Salespersons** with their **Sales **and the **Sales Target Amount.** Now I will count the cells which will be the same or greater than the Criteria Sales value.

**Step 1:** Enter the formula in cell **E4.Â **

`=COUNTIF(C4:C12,">="&D4)`

**Formula Breakdown**

Firstly, I have passed the range of our cells which is the **Sales** column. After that, I have compared each cell value with our criteria value which is in the **D4** cell. The whole formula will return results after comparing the values.

**Step 2:** After pressing Enter the result will be showed.

Thus, how we can compare numbers between two cells using the **COUNTIF** function.

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

**2. How to Count a Particular Time Using COUNTIFÂ **

Letâ€™s assume we have a dataset of a person named **John** with his daily cycling duration. The duration is in **hh:mm:ss** format which is the first hour the minutes then the second in the sequential format. Now I will show how to count the cells which are greater or equal to the cell value **D4**.

**Step 1:** Enter the formula in cell **E4** and press **Enter**.

`=COUNTIF(C4:C12,">="&D4)`

**Formula Explanation**

Firstly I have passed the range of our cells which is the **Duration** column. After that, I have compared each cell value with our criteria value which is in the **D4** cell. The whole formula will return results after comparing the values.

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

**3. How to Count Cells within a Range Using COUNTIF**

Now we will find out or count cells a range of cells using the **COUNTIF** function. For this, we will use the same example which was used in method 1. But here I will show how to find out the number of sales where our target sales are **between $150 and $500.**

**Step 1:** Enter the formula in cell **E4 **and press **Enter**.

`=COUNTIF(C4:C12,">"&D4)-COUNTIF(C4:C12,">"&D5)`

**Formula Explanation**

In the first part of the formula **=COUNTIF(C4:C12,â€ť>â€ť&D4)** returns the values which are greater than $150 and **COUNTIF(C4:C12,â€ť>â€ť&D5)** returns the values greater than $500. After that, by subtracting these two values we are getting the values between $150 and $500.

**[ Note: This method applies to any range of dates and other numeric variables.]**

**Read More:** **How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)**

**Similar Readings**

**COUNTIF Date Is within 7 Days****COUNTIF Between Two Dates in Excel****How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)****COUNTIF Multiple Ranges Same Criteria in Excel**

**4. How to do OR Operation Using COUNTIF.**

Sometimes we may need to search different items from any dataset. This way we can use **COUNTIF** functions to search multiple things at a time. Letâ€™s have a dataset of some foods. Now we will count how many food items are related to **Salad and Pasta**.

**Step 1: **Enter the formula in cell **D4 **and press **Enter**.

`=COUNTIF(B4:B11,"Pasta")+COUNTIF(B4:B11,"*salad")`

**Formula Explanation**

Here in the part, we are finding the food names which is started as **Pasta** from the Food **Names** column using **COUNTIF(B4:B11,â€ťPastaâ€ť)** formula. After that, we are searching the names which last part is as **salad** using **COUNTIF(B4:B11,â€ť*saladâ€ť)** formula. Then add the values returned by these two sub-functions.

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

### 5. Enter the formula for OR operation using COUNTIF

In this section, we will see how to find duplicate and Unique values using the **COUNTIF** function. For this letâ€™s consider we have a dataset of some employees with their ID. Now I will show how to find duplicates and unique values using the **COUNTIF** function.

**Step 1:** Enter the formula in cell **D4** and press **Enter**.

`=SUMPRODUCT((COUNTIF(B4:B17,B4:B17)>1)*(B4:B17<>""))`

**Formula Explanation**

Here in the first inner function **COUNTIF(B4:B17, B4:B17)>1** is finding the duplicates by comparing each cell with another in the Names cell and **(B4:B17<>â€ťâ€ť)** is checking if two values are not equal to each other. Additionally here is used SUMPRODUCT function. Which syntax is like this:

`SUMPRODUCT(array1, [array2], [array3], ...)`

It takes the array in its parameter and returns the sum. In this formula, we are adding all the values which are returned by **(COUNTIF(B4:B17, B4:B17)>1)*(B4:B17<>â€ťâ€ť)** this part.

**Step 2:** Same for the Unique values. First, enter the formula in cell **E4** and press **Enter**.

`=SUMPRODUCT((COUNTIF(B4:B17,B4:B17)=1)*(B4:B17<>""))`

**Formula Explanation**

This is the same as the previous formula. Instead of comparing the duplicates values here, we are finding the unique values by **(COUNTIF(B4:B17, B4:B17)=1)** this part.

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

**Conclusion**

These are the ways to use the **COUNTIF** function for two different cells in Excel. I have shown all the methods with their respective examples but there can be many other iterations. Also, I have discussed the fundamentals of these functions and their most commonly used format codes. If you have any other method of achieving this, please share it with us.