In this article, I will tell you how to count the number of cells in a cell range that cell value between or not between 2 values ( number or date ).

### 1. How To Count Cells Between Two Values In Excel.

- We will use the excel function
**COUNTIFSĀ**to count cells between two values. - Below is the example data cells. Column
**A**contains the employee name, column**B**contains the employee salary, and column**C**contains the employee birthday.

**A****B****C****D****E****F****1****Employee****Salary****Birthday****Count Values****2**Jerry 8000 1/1/2001 **salary_min**7500 **3**Kate 8200 10/1/1998 **salary_max**8500 **4**Tom 9000 7/1/1999 **Result_1**2 **5**Lily 6000 5/16/2001 **6**john 7000 6/19/2003 **birthday_min**9/1/1999 **7**Richard 5600 9/9/2000 **birthday_max**9/1/2001 **8**Jackie 6500 9/18/2001 **Result_2**3 - Column
**E**contains the criteria column name, and column**F**contains the**maximum**and**minimum**criteria column values. - We will use the excel function
**COUNTIFS(cell_range1, criteria1, cell_range2, criteria2)**to count the cells number between the above values. - Input the formula
**=COUNTIFS(B2:B8,”>=”&F2,B2:B8,”<=”&F3)**in cell**F4**, then it will calculate out the number of cells which salary value between**7500**and**8500**. When you press the enter key, it will display number**2**in cell**F4**. - Input the formula
**=COUNTIFS(C2:C8,”>=”&F6,C2:C8,”<=”&F7)**in cell**F8**, then it will calculate out the number of cells which birthday value between**9/1/1999**and**9/1/2001**. When you press the enter key, it will display number**3**in cell**F8**.

### 2. How To Count Cells Not Between Two Values In Excel.

- We can use the excel function
**COUNTIF(cell_range, criteria)**to count the cell numbers which not between 2 values. - There are 2 edge value in the value range, so you can count the cell numbers less than the minimum value and greater than the maximum value separately, and then add the 2 values to get the final result value.
- Input the formula
**=COUNTIF(B2:B8, “<=”&F2) + COUNTIF(B2:B8,”>=”&F3)**in cell**F4**of the above table, the formula will add the 2 cell numbers that not between**7500**and**8500**. - When you press the enter key, it will display number
**5**in cell**F4**. - Input the formula
**=COUNTIF(C2:C8, “<=”&F6) + COUNTIF(C2:C8,”>=”&F7)**in cell**F8**, the formula will add the 2 cell numbers that not between**9/1/1999**and**9/1/2001**. - When you press the enter key, it will display number
**4**in cell**F8**. - You can also use the excel function
**SUMPRODUCTĀ**to count the cell numbers that not between the 2 values. - Enter the formula
**=SUMPRODUCT((B2:B8<F2) + (B2:B8>F3))**in cell**F4**and press enter key, then it will display the number**5**in cell**F4**. - The formula first calculate
**(B2:B8<F2)**which returns the array**{FALSE, FALSE,FALSE,TRUE,TRUE,TRUE,TRUE}**, you can input the formula**=(B2:B8<F2)**in any cell to verify it. - So the expression
**(B2:B8<F2) + (B2:B8>F3)**will return the array**{0,0,1,1,1,1,1}**, you can also input the formula**=(B2:B8<F2) + (B2:B8>F3)**in any cell to verify this. - Then the function
**SUMPRODUCTĀ**will add all the array elements together and return the number**5**. - So you can also input the formulaĀ
**=SUMPRODUCT((C2:C8<F2) + (C2:C8>F3))**in cell**F8**and press enter key, then it will display the number**4**in cell**F8**.