COUNTIF between Two Cell Values in Excel (5 Examples)

Find compared values using COUNTIF

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

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.

How to Count Cells by Comparing Two Cells Numbers Using COUNTIF

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.

Enter the formula using COUNTIF in cell E4

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

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.

Count time using Countif function

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.

Enter the formula in cell E4 and press Enter

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.

How to Count Cells within a Range Using COUNTIF

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.

Enter the formual using Countif to find the range

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

 How to do OR Operation Using COUNTIF

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.

Enter the formula for OR operation using COUNTIF

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.

How to Find Duplicates and Unique Values Using COUNTIF

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.

Find duplicate using COUNTIF

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.

find unique value using countif function

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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo