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 are the example data cells. Column
**A**contains the employee’s name, column**B**contains the employee’s salary, and column**C**contains the employee’s birthday.

- 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 cell 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 whose salary value is 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 whose 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 values 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 are 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 are 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 are 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 calculates
**(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**.