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.

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

**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 is applicable for any range of dates and other numeric variables. ] **

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

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

**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 then please feel free to share it with us.