How to Use COUNTIF Between Two Numbers (4 Methods)

Sometimes while working in Microsoft Excel we need to count cells between two numbers. We can do this with the COUNTIF function. The COUNTIF function is a statistical function. It counts the number of cells that meet a criterion. In this article, we will describe 4 methods of how to use the COUNTIF function between two numbers with easy examples and explanations.

COUNTIF Function in Excel: Syntax

➤ Description

Count cells within specific criteria.

➤ Generic Syntax

COUNTIF(range,criteria)

➤ Argument Description

ARGUMENT	REQUIREMENT	EXPLANATION
range	Required	The number of cells we want to count according to the criteria.
criteria	Required	The criteria that we will use to determine which cells to count.

➤ Returns

The return value of the COUNTIF function is numeric.

➤ Available in

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.

1. Using the COUNTIF Function to Count Cell Numbers Between Two Numbers

Suppose we have a dataset of 6 students with their marks. Here, we will count the number of students for two specific marks. In this example, we will count for the marks ‘>=70’ and ‘<80’. Let’s see how can we do this:

Firstly, select cell F7.
Now insert the following formula:

=COUNTIF(C5:C10,"<"& 80)

Press Enter.
So, we will get the number of students who got marks greater than or equal to 70. Here, the total number of students under the criteria is 4.

Next, insert the following formula in cell F8:

=COUNTIF(C5:C10,"<"& 80)

Press Enter.
Finally, this will return the number of students 3 in cell F8.

2. Using the COUNTIF Formula between Two Number Ranges

Now we want to calculate the number of students for two number ranges. In this case, the COUNTIF formula is applicable. Because this formula can return values by counting the values between two ranges. We will use the dataset of our previous example for this method. Let’s see the process to do this:

In the beginning, select cell F7.
Insert the following formula:

=COUNTIF(C5:C10,">="&C12)-COUNTIF(C5:C10,">="&C13)

Then press Enter.
Hence, It returns the number of total students within the range >=50 & <=80 which is 3.

Next, insert the following formula in cell F8:

=COUNTIF(C5:C10,">="&40)-COUNTIF(C5:C10,">="&60)

Again, Type this formula in cell F9:

=COUNTIF(C5:C10,">="&70)-COUNTIF(C5:C10,">="&90)

Press Enter.
As a result, we can see the total number of students in cells F8 and F9 under the range >=40 & <=60 and >=70 & <=90 respectively. They are 1 & 4.

🔎 How Does the Formula Work?

COUNTIF(C5:C10,”>=”&C13): Calculates the number of students with more than 80 marks.
COUNTIF(C5:C10,”>=”&C12): This part gives the student’s count who got more than 50 marks.
COUNTIF(C5:C10,”>=”&C12)-COUNTIF(C5:C10,”>=”&C13): Returns the total count of students within the range >=50 & >=80.

Similar Readings

3. Applying the Excel COUNTIF Function Between Two Dates

We can use the COUNTIF function to count the number of cells between two dates also. For example, we have a dataset of dates with corresponding sales data. In this example, we are going to count the dates between two dates as well as for a single date. Let’s see how we can do this:

First, select cell F7.
Now type the following formula:

=COUNTIF(B5:B10,">="&C12)

Hit Enter.
Here, we can see the number of date cells under the range >=10-01-22 in cell F7. It is 5.

Next, we will count the dates in between the range >=10-01-22 and <=12-01-22. To do this just follow the below steps:

Select, cell F8.
Put the below formula in cell F8:

=COUNTIF(B5:B10,">="&C12)-COUNTIF(B5:B10,">="&C13)

Then press Enter.
Finally, it will give the number of dates in return within the range >=10-01-22 and <=12-01-22 and it is 2.

🔎 How Does the Formula Work?

COUNTIF(B5:B10,”>=”&C13): Counts the number of dates less than the value of cell C13.
COUNTIF(B5:B10,”>=”&C12): Finds the total number of dates less than cell C12.
COUNTIF(B5:B10,”>=”&C12)-COUNTIF(B5:B10,”>=”&C13): Returns the number of dates within the range >=10-01-22 and <=12-01-22.

4. Applying the COUNTIF Function to Count a Particular Time Between Two Numbers

With the use of the COUNTIF function, we can count a particular time also. For this example, we have the following dataset. The dataset consists of dates and working hours on each day. This process will calculate the number of dates for a specific time range. In the following figure, we have 3-time ranges. Let’s calculate the number of dates for each time range.

Firstly, select the cell G7.
Secondly, write down the following formula:

=COUNTIF(C5:C10,">="&F7)

Then press Enter.
Here, it will return the total number of dates 2. It means the working hours are less than 5:00:00 on two dates.

After that, put the formulas given below in cells H8 & H9.
For H8:

=COUNTIF(C5:C10,">="&F8)

For H9:

=COUNTIF(C5:C10,"<="&F8)

Finally, press Enter. We can see the Date Count values for the other two ranges >=6:00:00 and <=6:00:00 respectively. They are 1 & 5.

Download Practice Workbook

You can download the practice workbook from here.

Use COUNTIF Between Two.xlsx

Conclusion

In the end, by following these methods we can use the COUNTIF function between two numbers. There is a practice workbook added with this article. So, download the workbook and practice yourself. If you feel any confusion just leave a comment in the below box.