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!

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.