Md. Abdullah Al Murad
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, and specific time. In this article, we will discuss the various uses of COUNTIF between two cell values in Excel. If you are also curious about it download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
5 Suitable Examples to Use COUNTIF Function Between Two Cell Values in Excel
To demonstrate the examples, we consider a dataset of 10 people. We will set different types of criteria to use the COUNTIF function between two cell values. Our dataset is in the range of cells B5:C14.
📚 Note:
All the operations of this article are accomplished by using the Microsoft Office 365 application.
1. Apply COUNTIF Function to Count Cells by Comparing Numbers
In the first example, we will use the COUNTIF function to count numbers that are greater than our target value. Our desired value is in cell D5.
The steps to complete the example are given below:
📌 Steps:
- First of all, select cell E5.
- Now, write down the following formula in the cell.
=COUNTIF(C5:C13,">="&D5)
- Press Enter.
- You will see the number of values that are greater than $150.
Thus, we can say that our formula works perfectly, and we are able to use the COUNTIF function between two cell values in Excel.
Read More: How to Use COUNTIF Between Two Numbers (4 Methods)
2. Counting Particular Time by Using COUNTIF Function
In this example, we are going to use the COUNTIF function to count the particular time that is greater than our desired time value. Our desired value is in cell D5.
The steps to do the operation are given as follows:
📌 Steps:
- First, select cell E5.
- After that, write down the following formula in the cell.
=COUNTIF(C5:C13,">="&D5)
- Then, press Enter.
- You will get the time values that are greater than 1 hour.
Hence, we can say that our formula works effectively, and we are able to use the COUNTIF function between two cell values in Excel.
Read More: COUNTIF Greater Than and Less Than [with Free Template]
3. Using COUNTIF Function to Count Cells Within a Range
In the following example, the COUNTIF function will help us to count the number of particular cells within a data range. Our desired data range is in the range of cells D5:D6.
The steps to do the operation are shown below:
📌 Steps:
- At first, select cell E5.
- Afterward, write down the following formula in the cell.
=COUNTIF(C5:C13,">"&D5)-COUNTIF(C5:C13,">"&D6)
- Next, press Enter.
- You will notice the function will count the number of values within our data range.
Therefore, we can say that our formula works precisely, and we are able to use the COUNTIF function between two cell values in Excel.
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. Apply OR Operation Through COUNTIF Function
Here, we will do the OR operation by the COUNTIF function. To accomplish the operation, we have to take a different dataset of food items and their cost.
The steps to finish the OR operation are described below:
📌 Steps:
- Firstly, select cell D5.
- Next, write down the following formula in the cell.
=COUNTIF(B5:B10,"Pasta")+COUNTIF(B5:B10,"*salad")
- After that, press the Enter key.
- You will get the desired result.
So, we can say that our formula works fruitfully, and we are able to use the COUNTIF function between two cell values through the OR operation in Excel.
Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
5. Combination of SUMPRODUCT and COUNTIF Functions with OR Logic
In the last example, we will the SUMPPRODUCT and COUNTIF functions to count numbers and apply the OR logic. To do this, we will use another different dataset of 10 employees and their IDs. We will calculate the number of duplicate IDs and unique IDs by the functions.
The steps to do the OR logic are shown as follows:
📌 Steps:
- First of all, select cell D5 to calculate the duplicate ID numbers.
- For that, write down the following formula in the cell.
=SUMPRODUCT((COUNTIF(B5:B14,B5:B14)>1)*(B5:B14<>""))
- Then, press Enter.
- After that, we have to calculate the unique ID.
- Now, select cell E5 and write down the following formula in the cell.
=SUMPRODUCT((COUNTIF(B5:B14,B5:B14)=1)*(B5:B14<>""))
- Again, press Enter.
- You will get both values at our desired cells.
Finally, we can say that our formula works successfully, and we are able to use the COUNTIF function between two cell values by the OR logic in Excel.
Read More: How to Use COUNTIF to Count Cells Greater Than 0 in Excel
Using COUNTIF Function to Count Value for Multiple Criteria
In addition, to the earlier examples, we are going to demonstrate three more closely related examples here. Among them, in the first example, we are going to use the COUNTIF function to count values for multiple criteria. Our criteria are in the range of cells D5:D6.
The procedure is explained below step-by-step:
📌 Steps:
- At the start, select cell E5.
- After that, write down the following formula in the cell.
=COUNTIF(C5:C13,">"&D5)-COUNTIF(C5:C13,">"&D6)
- Press Enter.
- You will get the number of entities lies between $150 and $600.
At last, we can say that our formula works perfectly, and we are able to use the COUNTIF function between two cell values for multiple criteria in Excel.
Finding Values Between Two Dates by COUNTIF Function
In this example, we will find values between two dates by the COUNTIF function. Our desired dates are in the range of cells D5:D6.
The process is described below:
📌 Steps:
- First, select cell E5.
- Now, write down the following formula in the cell.
=COUNTIF(C5:C13,">"&D5)-COUNTIF(C5:C13,">"&D6)
- Press Enter.
- Finally, you will get the number of entities lying between 1 hour and 2 hours.
In the end, we can say that our formula works effectively, and we are able to use the COUNTIF function between two date cell values in Excel.
Use of COUNTIF Function for Two Numerical Numbers
In the last example, we are going to find the number of numerical values between two numbers through the COUNTIF function. Our desired numerical numbers are in the range of cells D5:D6.
The method is explained below step-by-step:
📌 Steps:
- At first, select cell E5.
- Afterward, write down the following formula in the cell.
=COUNTIF(C5:C13,">"&D5)-COUNTIF(C5:C13,">"&D6)
- Next, press Enter.
- You will find the number of entities lying between numbers 100 and 500.
Finally, we can say that our formula works successfully, and we are able to use the COUNTIF function between two numerical cell values in Excel.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to the COUNTLIF function between two cell values in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!
