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.

Subscribe to learn more excel tricks.

We don’t spam!

Subscribe to learn more excel tricks.

We don’t spam!

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.