Conditional Aggregation
Count Rows Meeting Internal Column-vs-Column Criteria (SUMPRODUCT)
Count table rows where one column exceeds or precedes another column on the same row—such as Actual > Budget AND Ship_Date <= Due_Date—using Boolean SUMPRODUCT.
Why COUNTIFS fails when comparing two columns row by row!
COUNTIFS requires its second argument in every pair to be a scalar threshold (like ">500" or ">=" & F2)—if you pass a second range such as COUNTIFS(C2:C7, ">" & B2:B7), it fails to evaluate row-by-row comparisons cleanly! Whenever an operational audit asks "How many work orders finished ON OR BEFORE their promised due date AND stayed AT OR UNDER their approved cost budget?", Boolean array multiplication inside SUMPRODUCT((C2:C7 <= B2:B7) * (E2:E7 <= D2:B7)) compares paired cells row by row and sums the 1s!
Look at six manufacturing work orders below: four orders stayed within cost budget (C <= B), four shipped on or before due date (E <= D), and exactly 3 orders satisfied BOTH internal SLA conditions simultaneously!
| Order | Budget (B) vs Actual (C) | Due Day (D) vs Ship Day (E) | On-Budget & On-Time Flag |
|---|---|---|---|
| WO-101 | 12,000 vs 11,450 (OK) | Day 15 vs Day 14 (OK) | 1 * 1 = 1 (Compliant) |
| WO-102 | 8,500 vs 9,120 (Over!) | Day 18 vs Day 17 (OK) | 0 * 1 = 0 (Cost Overrun) |
| WO-104 | 15,000 vs 14,200 (OK) | Day 20 vs Day 23 (Late!) | 1 * 0 = 0 (Late Shipment) |
Count Rows Satisfying Two Internal Column-vs-Column Conditions
Multiplies two equal-length Boolean array comparisons (Actual_Cost <= Budget_Cost) and (Ship_Day <= Due_Day), returning the exact count of fully compliant rows.
=SUMPRODUCT((C2:C7 <= B2:B7) * (E2:E7 <= D2:D7))Count Cost-Overrun Rows (Actual_Cost > Budget_Cost)
Coerces a single row-by-row comparison C2:C7 > B2:B7 with double-unary -- inside SUMPRODUCT.
=SUMPRODUCT(--(C2:C7 > B2:B7))Try the example
Use the original values or change them to test your understanding.