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.

**Table of Contents**hide

**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:

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

**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:

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

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

**Similar Readings**

**COUNTIF Excel Example (22 Examples)****How to Use COUNTIF with WEEKDAY in Excel****Use Excel COUNTIF That Does Not Contain Multiple Criteria****How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)**

**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:

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

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

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

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

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.