SUMPRODUCT in Google Sheets
Use SUMPRODUCT to multiply ranges together and add up the results in one step — perfect for weighted totals and conditional sums.
=SUMPRODUCT(A2:A100, B2:B100)
Multiplies each A by the matching B and adds all the products — e.g. quantity times price for a grand total.
How it works
SUMPRODUCT multiplies the values in matching positions of two or more equally-sized ranges, then sums every product. Because it works on whole arrays, you can also feed it TRUE/FALSE conditions — Sheets treats TRUE as 1 and FALSE as 0 — which turns it into a flexible conditional counter or sum without needing COUNTIFS. Just make sure every range is exactly the same size.
Variations
Conditional sum with a condition
=SUMPRODUCT((A2:A100="North")*B2:B100)
Sums B only where A equals "North". The comparison makes an array of 1s and 0s.
Count rows meeting two conditions
=SUMPRODUCT((A2:A100="North")*(B2:B100>100))
Multiplying two TRUE/FALSE arrays counts rows where both hold.
Weighted average
=SUMPRODUCT(A2:A100, B2:B100)/SUM(B2:B100)
Values in A weighted by the weights in B.
Examples
| Scenario | Formula |
|---|---|
| Total order value (qty x price) | =SUMPRODUCT(B2:B, C2:C) |
| Sales for one product this year | =SUMPRODUCT((A2:A="Widget")*(YEAR(D2:D)=2026)*E2:E) |
FAQ
Why do I get a #VALUE! error in SUMPRODUCT?
The ranges are different sizes, or one contains text where a number is expected. Make every range the same height and width.
When should I use SUMPRODUCT instead of SUMIFS?
Use SUMPRODUCT for calculations that multiply ranges (weighted totals) or for conditions SUMIFS cannot express, like comparing two columns to each other.
Do I need to press Ctrl+Shift+Enter like in Excel?
No. Google Sheets handles SUMPRODUCT as a normal formula — just press Enter.
Related formulas
- #REF! Error in Google Sheets — What It Means and How to Fix It
- #VALUE! Error in Google Sheets — Causes and Fixes
- COUNTIF vs COUNTIFS in Google Sheets — When to Use Each
- FILTER vs QUERY in Google Sheets — Which Should You Use
- IF vs IFS in Google Sheets — When to Use Each
- INDEX MATCH vs VLOOKUP in Google Sheets — Which Is Better
- SUMIF vs SUMIFS in Google Sheets — When to Use Each
- TEXTJOIN vs CONCATENATE in Google Sheets — Which to Use