Conditional Aggregation
Count Cells Greater Than or Less Than a Cell Reference (COUNTIF & COUNTIFS)
Concatenate comparison operators with cell references—COUNTIF(range, ">" & E2), COUNTIF(range, "<" & E2), and two-sided COUNTIFS(range, ">=" & E2, range, "<=" & F2)—without falling into the literal ">E2" string trap.
Avoid the #1 COUNTIF beginner mistake: never put cell references inside quotes!
Writing =COUNTIF(B2:B7, ">80") works fine for a hardcoded number, so analysts naturally try =COUNTIF(B2:B7, ">E2") when the threshold lives in cell E2—and get 0 every single time! Why? Inside double quotes, ">E2" tells Excel to compare against the literal text string "E2" rather than the numeric value stored inside cell E2! (Consolidates both "Count cells greater than" and "Count cells less than" threshold patterns.)
Always close the quotes right after the comparison operator and join the cell reference with the ampersand (&): COUNTIF(B2:B7, ">" & E2) evaluates ">" & 80 -> ">80" dynamically! Combine both ends inside COUNTIFS(B2:B7, ">=" & E2, B2:B7, "<=" & E3) to count SLA tickets, exam scores, or sensor readings falling inside an inclusive [Min, Max] target band!
| Threshold Comparison Goal (Scores: 64, 78, 80, 88, 93, 97) | Dynamic Cell Formula (E2 = 80, E3 = 95) | Matched Count |
|---|---|---|
| Strictly Greater Than E2 (> 80) | =COUNTIF(B2:B7, ">" & E2) | 3 (88, 93, 97) |
| Strictly Less Than E2 (< 80) | =COUNTIF(B2:B7, "<" & E2) | 2 (64, 78) |
| Inclusive Target Band [80, 95] | =COUNTIFS(B2:B7, ">=" & E2, B2:B7, "<=" & E3) | 3 (80, 88, 93) |
Count Values Strictly Greater Than Dynamic Cell E2
Concatenates ">" & E2 so changing E2 immediately updates the count without editing formulas.
=COUNTIF(B2:B7, ">" & E2)Count Values Strictly Less Than E2 or Inside Inclusive Band [E2, E3]
Pairs ">=" & E2 with "<=" & E3 inside COUNTIFS to count values within closed bounds.
=COUNTIFS(B2:B7, ">=" & E2, B2:B7, "<=" & E3)Try the example
Use the original values or change them to test your understanding.