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 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.
    how-to-count-cells-between-or-not-between-two-values-in-excel
  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 cell 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 whose salary value is 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 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.

  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 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.
  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 are 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 are 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 are 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 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.
  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.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.