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.