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.


Download Practice Workbook

You can download the practice workbook from here.


Overview of Excel COUNTIF Function

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


4 Methods to Use COUNTIF Between Two Numbers

1. Use of 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:

Use of COUNTIF Function to Count Cell Numbers Between Two Numbers

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

Use of COUNTIF Function to Count Cell Numbers Between Two Numbers

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

Read more: COUNTIF Greater Than and Less Than


2. COUNTIF Formula with 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:

COUNTIF Formula with Two Number Ranges

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

COUNTIF Formula with Two Number Ranges

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

Read more: How to Use COUNTIF to Count Cells Greater Than 0 in Excel


Similar Readings


3. Apply 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:

Apply COUNTIF Function Between Two Dates

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

Apply COUNTIF Function Between Two Dates

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.

Read more: COUNTIF Date Is within 7 Days


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

COUNTIF Function to Count a Particular Time

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

COUNTIF Function to Count a Particular Time

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


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.


Further Readings

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

2 Comments
  1. There seems to be a logical error when counting cells with values between two given numbers.
    The result “3”, pshowed in the students’ marks example (>=50 and <=80) should be 4…

    • Thanks for your feedback sir.
      The range is (>=50 and <=80). In Excel COUNTIF function by default omit the upper end value.
      So, the formula is basically showing the result for the values less than 80.

Leave a reply

ExcelDemy
Logo