SUMPRODUCT
Weighted averages and multi-condition sums in one cell.
Why planners need it
The planner’s workhorse. Use it for a volume-weighted AHT across queues, or to sum/count against several conditions without helper columns.
Syntax
=SUMPRODUCT(Calls, AHT) / SUM(Calls)
Worked example
Three queues with their calls and AHT. A plain average of AHT (250s) is wrong because the queues aren’t equal size. Weight by volume:
| Queue | Calls | AHT (s) |
|---|---|---|
| Sales | 120 | 240 |
| Service | 80 | 300 |
| Tech | 200 | 210 |
=SUMPRODUCT(B2:B4, C2:C4) / SUM(B2:B4) → 236.7s — the true blended AHT, not the flat 250s.
Watch out: Arrays must be the same shape/size, or it returns #VALUE!. For conditions, multiply boolean tests:
=SUMPRODUCT((Queue="Sales")*Calls).