SUMPRODUCT()
Multiplies corresponding items across arrays and adds up the results — a flexible way to do conditional sums without helper columns.
SUMPRODUCT(array1, [array2], [array3], ...)SUMPRODUCT multiplies each element of array1 by the matching element of array2 (and so on), then sums the products. With one array, it just sums it like SUM.
It's a favorite for conditional counting and summing because comparisons like (A2:A20="North") produce an array of TRUE/FALSE (treated as 1/0) that SUMPRODUCT can multiply against other arrays — all without pressing Ctrl+Shift+Enter.
Arguments
- array1
- The first array or range.
- array2, ...optional
- Additional arrays or ranges, the same size as array1.
Examples
=SUMPRODUCT((A2:A20="North")*(C2:C20))→18,400
Sums column C only where column A equals North — an alternative to SUMIF.
=SUMPRODUCT(B2:B10, C2:C10)→5,430
Multiplies quantity by price row-by-row, then totals it — a quick line-item total.