SUMPRODUCT in Google Sheets multiplies corresponding values in arrays and returns the sum of those products. It is useful for weighted averages, conditional sums, and array-style calculations without entering array formulas.
1. Basic syntax
=SUMPRODUCT(array1, [array2], ...)
- Each array must have the same dimensions.
- SUMPRODUCT multiplies the first element of each array, then the second, and so on, then sums the results.
2. Example: weighted average
If you have quantities in column A and unit prices in column B, total value is:
=SUMPRODUCT(A2:A10, B2:B10)
3. Conditional sums (array-style)
You can combine SUMPRODUCT with conditions using (range = criterion) to get 1s and 0s:
=SUMPRODUCT((A2:A10="Yes")*(B2:B10))
This sums values in B where A is “Yes”.
4. Tips and pitfalls
- Keep ranges the same size and shape.
- Text or empty cells in numeric ranges are treated as zero.
- For very large ranges, consider FILTER + SUM or QUERY for readability.
Add your full guide content above this line. Then run npm run build (or your Astro build command) so the page is generated under /content/how-to-use-the-sumproduct-function-in-google-spreadsheets-a-comprehensive-guide.
