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.