How To Count Cells Between Or Not Between Two Values In Excel

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.

1. We will use the excel function COUNTIFS to count cells between two values.
2. 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

3. Column E contains the criteria column name, and column F contains the maximum and minimum criteria column values.
4. We will use the excel function COUNTIFS(cell_range1, criteria1, cell_range2, criteria2) to count the cells number between the above values.
5. 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.
6. 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.

1. We can use the excel function COUNTIF(cell_range, criteria) to count the cell numbers which not between 2 values.
2. 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.
3. 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.
4. When you press the enter key, it will display number 5 in cell F4.
5. 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.
6. When you press the enter key, it will display number 4 in cell F8.
7. You can also use the excel function SUMPRODUCT to count the cell numbers that not between the 2 values.
8. 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.
9. 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.
10. 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.
11. Then the function SUMPRODUCT will add all the array elements together and return the number 5.
12. 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.