Arte SheetsLearn. Try. Understand.

Logical & Conditional

Flag & Count Values NOT Between X and Y (OR & Two-Tail COUNTIF)

Flag and count numeric measurements strictly outside a chosen inclusive range, even when the two bounds are entered in reverse order.

Catch both low-end and high-end spec violations even if X and Y are entered backwards

Quality-control engineers, pharmacy cold-chain monitors (2.0°C to 8.0°C vaccine fridges), and financial audit rules constantly test whether a measurement falls NOT between X and Y—meaning strictly below the lower limit OR strictly above the upper limit. The thresholds in this example are illustrative; an in-range reading does not establish product safety, and the formula does not evaluate how long a temperature excursion lasted. For vaccines, follow the manufacturer and local protocol, including immediate action on excursions; do not infer usability from this example.

Writing OR(B2 < MIN($E$2, $E$3), B2 > MAX($E$2, $E$3)) improves on naive OR(B2 < $E$2, B2 > $E$3) in one huge practical way: wrapping the bounds in MIN and MAX makes the rule immune to swapped user inputs (if someone types 8.0 in E2 and 2.0 in E3, MIN = 2.0 and MAX = 8.0 still work seamlessly). Pair it with COUNTIF(B2:B7, "<" & MIN(E2, E3)) + COUNTIF(B2:B7, ">" & MAX(E2, E3)) to tally every low and high excursion across the batch.

Vaccine Fridge Probe (°C, Safe Window [2.0, 8.0])Out-of-Bounds Test OR(< Min, > Max)Excursion Direction
1.4 °C (Sub-Threshold Freeze Risk)TRUEBelow Lower Spec (< 2.0 °C)
2.0 °C and 5.6 °C (Boundary & Nominal)FALSEWithin Example Range [2.0, 8.0]
8.9 °C and 9.5 °C (Door-Open Warm Spike)TRUEAbove Upper Spec (> 8.0 °C) -> 3 Total Excursions

Swap-Safe Row Flag for Value Strictly Outside [X, Y]

Returns TRUE when B2 is strictly below MIN($E$2, $E$3) or strictly above MAX($E$2, $E$3), and FALSE on exact boundaries.

=IF(COUNT(B2, $E$2, $E$3) < 3, "Missing numeric input", OR(B2 < MIN($E$2, $E$3), B2 > MAX($E$2, $E$3)))

Count Total Two-Sided Out-of-Bounds Readings Across Column B2:B7

Counts only numeric readings outside the two numeric limits. Check missing readings separately; this is not a determination of vaccine safety.

=IF(COUNT(E2:E3) <> 2, "Missing numeric limits", SUMPRODUCT(--ISNUMBER(B2:B7), --(((B2:B7 < MIN(E2, E3)) + (B2:B7 > MAX(E2, E3))) > 0)))

Try the example

Use the original values or change them to test your understanding.

Further reading