MS Excel is the top source to compute different values and analyze tons of data. When it comes to counting values added in the cells, Excel tends to have multiple functions. Your data may include text values, numbers, dates, characters, or anything else. Here you will get to know how to CountIf between two numbers or values.
Before proceeding, let’s have a look at what this function is and how it works.
What is COUNTIF Function and How does it Work?
Excel users must have an idea about the COUNTIF function that is used to count numbers of different cells including values. With the help of a formula, you can easily count the number of a cell having different values between two numbers. These values could be in a specific range of cells or columns. You may use it with two COUNTIF functions given in the same value.
As you already know, this function is made to count cells and for two different criteria, all cells in the given range have to be compatible with both criteria to be in the function.
Generic Formula
=COUNTIFs(range,”>=smallest_num”,range,”<=largest_num”)
Let’s know about Arguments:
range: A range has values that count the number of cells that exists between two values.
smallest_num: It is the smallest number that counts the cells.
largest_num: It is the largest number that counts the cells.
Explanation
Using this formula lets you help in counting the number of cells that consist of values between two numbers.
How to Use CountIf Between Two Numbers
With an example, you will easily understand the formula. Let’s use a small subtract logic with the COUNTIF function.
At first, it helps in counting each number bigger than 50. Suppose in the list we have numbers such as 85, 75, 60, 55, 46, 62, 67, 28, 45, 30. It simply means that 6 numbers are bigger than 50 which simply explains the functioning of the first part of the formula.
=COUNTIF(B2:B11,”>=50”)
Furthermore, in the second step, we will have to count all the numbers bigger than 80 while subtracting them from the first count. In this list, we found just 1 figure bigger than 80, which is 85.
-COUNTIF(B2:B11,”>80”)
In a nutshell, 6-1=5, and that’s what we are supposed to have. Let’s assume, you want to count the number of students who scored between 50 to 80. While putting the figures in the formula, let’s have a look at it.
The complete formula will appear like this:
=COUNTIF(B2:B11,”>=50”)-COUNTIF(B2:B11,”>80)
Rather than applying a particular range of cells, you may want to count the whole column. In that case, your formula will appear like this:
=COUNTIF(B:B,”>=50”)-COUNTIF(B:B,”>80”)
In case, if you need to choose the first, ending, or between numbers given in different cells, you should try applying them right in the formula. And the formula will appear like this:
=COUNTIF(B2:B11,”>”&E1)-COUNTIF(B2:B11,”>”&F1)
Do you get an idea of how to use COUNTIF between two numbers while using Excel?
You must have come to know how simple and easy this function is. Let’s dig out more to understand this function naturally.
How to Count Cells within a Range using COUNTIF
In this section, we will use the COUNTIF formula to count a range of cells. In the figure below, you can find the data given in which we will tend to find the number of sales when the targeted sales value comes up between $150 and $500.
- In cell E4, you have to apply the formula and press Enter key from the keyboard.
=COUNTIF(C4:C12,”>”&D4)-COUNTIF(C4:C12,”>”&D5)
How to Count Cells by Comparing Numbers and Dates with COUNTIF
In this section, you will get to know how to compare the cells with the COUNTIF function. Suppose that we are having a dataset containing Salespersons and the Sales together with the Sales Target Amount. Now, you need to count the cells having the same or bigger value than the Criteria Sales value.
- In cell E4, you need to add the Formula.
=COUNTIF(C4:C12,”>=”&D4)
- Once you press the Enter key, the result will appear on the screen.
This is how you can compare numbers between two cells with COUNTIF function.
Enter the Formula for OR Function with COUNTIF
Here, you will see how to find a unique or a duplicate value with the help of the COUNTIF function. Suppose we have a dataset of employees with their ID. Let’s operate the formula on this given dataset.
- In cell D4, you have to enter the formula and press Enter key.
=SUMPRODUCT ((COUNTIF(B4:B17, B4:B17)>1)*(B4:B17<>””))
- You need to repeat the procedure for finding the unique values. In cell E4, enter the formula and press Enter key.
=SUMPRODUCT ((COUNTIF(B4:B17,B4:B17)=1)*(B4:B17<>””))
This is how you can find duplicates of unique values.
How to Count a Particular Time with COUNTIF
In this part of the post, you will see another great usage of the COUNTIF function. Suppose that in the dataset we have a person John having a daily cycling duration. And the duration format appears in hh:mm:ss, which is hours, minutes, and then seconds sequentially. Let’s see how to count the cell greater than or equal to the value in cell D4.
- In the cell, E4 add the formula and press the Enter key from the keyboard.
=COUNTIF(C4:C12,”>=”&D4)
To Sum Up
That’s it. Some of the handy ways that help you COUNTIF between two numbers are mentioned above. You can try any of them and hopefully, you will find them effective. Each method is explained with examples that make your understanding easier. Keep practicing these methods and continue sharing them with your friends.
