Conditional Formatting & Rules
Highlight & Count Values Between Two Bounds: Order-Safe MIN/MAX & COUNTIFS
Test whether numbers or dates fall inside [Lower, Upper]—even if a user types Upper before Lower—using AND(B2 >= MIN($E$2,$E$3), B2 <= MAX($E$2,$E$3)) and COUNTIFS.
Why Excel’s built-in "Between" Conditional Formatting rule uses MIN/MAX under the hood!
If a user enters Bound_1 = 85 in E2 and Bound_2 = 60 in E3 (typing the high bound first!), a naive formula =AND(B2 >= $E$2, B2 <= $E$3) asks for numbers simultaneously >= 85 AND <= 60—returning 0 matches! Wrapping the bounds in MIN($E$2, $E$3) and MAX($E$2, $E$3) makes your custom Conditional Formatting rule and COUNTIFS range check completely order-independent!
Even better, comparing inclusive COUNTIFS(B2:B7, ">=" & MIN(E2,E3), B2:B7, "<=" & MAX(E2,E3)) against strict exclusive ">" and "<" shows immediately how boundary-exact readings (60 and 85) shift pass/fail counts!
| Readings [52, 60, 71, 85, 92, 78] vs Bounds (60, 85) | Inclusive [60, 85] Count | Strict Exclusive (60, 85) Count | Boundary Behavior |
|---|---|---|---|
| E2 = 60, E3 = 85 (Normal Order) | 4 readings (60, 71, 85, 78) | 2 readings (71, 78) | Boundary values 60 & 85 included vs excluded |
| E2 = 85, E3 = 60 (Inverted User Input!) | 4 readings (Order-Safe!) | 2 readings | MIN/MAX normalizes swapped inputs |
| E2 = 95, E3 = 100 (Above All Data) | 0 readings | 0 readings | Clean zero count |
Order-Safe Inclusive Between-Bounds Count via COUNTIFS + MIN/MAX
Counts values in B2:B7 satisfying MIN(E2, E3) <= x <= MAX(E2, E3) regardless of whether E2 or E3 holds the smaller bound.
=COUNTIFS(B2:B7, ">=" & MIN(E2, E3), B2:B7, "<=" & MAX(E2, E3))Strict Exclusive Between-Bounds Count (MIN < x < MAX)
Excludes exact boundary hits using ">" and "<" operators.
=COUNTIFS(B2:B7, ">" & MIN(E2, E3), B2:B7, "<" & MAX(E2, E3))Try the example
Use the original values or change them to test your understanding.