Statistical & Conditional Counting
Count Cells Not Between Two Numbers (Out-of-Tolerance Spec Gate)
Count numeric observations that fall strictly outside a [Lower, Upper] specification interval while ignoring blanks and guarding reversed min/max inputs with MIN and MAX.
Why COUNTIFS("<>" , "<" & Low) + COUNTIFS("<>" , ">" & High) guards blanks & reversed bounds
Because a single number cannot simultaneously be less than the lower specification limit (LSL) and greater than the upper specification limit (USL), the two tail events (< LSL) and (> USL) are mutually exclusive.
Adding an explicit "<>" non-blank criterion inside COUNTIFS(range, "<>", range, "<" & MIN(b1, b2)) + COUNTIFS(range, "<>", range, ">" & MAX(b1, b2)) prevents unpopulated rows from coercing to 0, while MIN and MAX protect the formula even if Upper is entered before Lower.
| Rule Variant | Boundary Behavior | Formula Pattern |
|---|---|---|
| Strictly outside [LSL, USL] | Boundary values pass (in-spec) | COUNTIFS(rng, "<>", rng, "<" & lo) + COUNTIFS(rng, "<>", rng, ">" & hi) |
| Outside or on boundary | Boundary values count as rejects | COUNTIFS(rng, "<>", rng, "<=" & lo) + COUNTIFS(rng, "<>", rng, ">=" & hi) |
| In-spec yield % | 1 - OutOfSpec / NumericCount | ROUND(1 - Rejects / COUNT(rng), 4) |
Swap-Safe Out-of-Band Count Formula
Normalize lo_lim = MIN(E2, E3) and hi_lim = MAX(E2, E3) inside LET and pair "<>" with each tail test.
=LET(lo_lim, MIN(E2, E3), hi_lim, MAX(E2, E3), COUNTIFS(B2:B8, "<>", B2:B8, "<" & lo_lim) + COUNTIFS(B2:B8, "<>", B2:B8, ">" & hi_lim))Try the example
Use the original values or change them to test your understanding.