Conditional Aggregation
Count Rows Where Column C Exceeds Column B (Row-by-Row SUMPRODUCT)
Count how many table rows satisfy an internal column-to-column test (such as Actual > Budget or Shipped_Days > SLA_Days) and sum the excess variance in one formula using SUMPRODUCT(--(C2:C6 > B2:B6)) without helper columns.
Why COUNTIF cannot compare two columns row by row—and how SUMPRODUCT solves it
Suppose column B holds each department’s Monthly Budget and column C holds its Actual Spend across 5 rows, and you want to count how many departments went over budget (C_i > B_i). COUNTIF and COUNTIFS only compare a range against a single scalar criterion—they cannot compare C2 against B2, C3 against B3, and C4 against B4 simultaneously without adding a helper column. The sample assumes both ranges contain numeric amounts for each row. Blank cells may be treated as zero, and text or error values can change the result or cause an error; validate and clean source data before using this calculation for reporting.
SUMPRODUCT evaluates vector arithmetic element-by-element across matching rows. Inside SUMPRODUCT(--(C2:C6 > B2:B6)), the comparison C2:C6 > B2:B6 produces a 5-row boolean array {TRUE; FALSE; FALSE; TRUE; TRUE}, the double-unary -- converts TRUE/FALSE into {1; 0; 0; 1; 1}, and SUMPRODUCT totals the 1s to return 3 over-budget departments—and multiplying by (C2:C6 - B2:B6) totals the exact dollar overrun ($26,000) in a single cell.
| Department | Budget B ($k) | Actual C ($k) | Internal Row Test (C > B) |
|---|---|---|---|
| Cloud Infra | 120 | 134 | TRUE (1) -> +$14k Overrun |
| Field Sales | 200 | 188 | FALSE (0) -> Under Budget |
| Customer Success | 95 | 95 | FALSE (0) -> Exact On-Budget |
| Paid Acquisition / R&D | 150 / 80 | 158 / 84 | TRUE (1) + TRUE (1) -> Total 3 Depts (+$26k) |
Count Rows Meeting Internal Column Comparison: SUMPRODUCT(--(C2:C6 > B2:B6))
Counts every row where Actual (C2:C6) strictly exceeds Budget (B2:B6) with zero helper columns.
=SUMPRODUCT(--(C2:C6 > B2:B6))Sum Total Overrun Only Across Over-Budget Rows: SUMPRODUCT((C > B) * (C - B))
Sums positive overruns (C_i - B_i) while ignoring under-budget savings so favorable variances do not mask cost overruns.
=SUMPRODUCT((C2:C6 > B2:B6) * (C2:C6 - B2:B6))Try the example
Use the original values or change them to test your understanding.