SUMPRODUCT multiplies corresponding values in one or more ranges and adds the results, making it useful for weighted calculations and criteria-based totals.
These examples show how to adapt the same core formula to everyday Excel and Google Sheets tasks.
How does SUMPRODUCT work?
The basic syntax is =SUMPRODUCT(array1,[array2],...).
Each array supplies values to multiply position by position; SUMPRODUCT then adds those products.
For example, if A2:A4 contains quantities of 2, 3, and 4, and B2:B4 contains prices of 10, 5, and 8, this formula returns 67:
=SUMPRODUCT(A2:A4,B2:B4)The calculation is (2 × 10) + (3 × 5) + (4 × 8).
The ranges must have compatible dimensions.
When there is just one array, SUMPRODUCT adds its numeric values.
SUMPRODUCT formula examples
1. Calculate a total from quantities and prices
Use two matching ranges to multiply each row’s quantity by its unit price, then sum the line totals.
If quantities are in B2:B20 and prices are in C2:C20:
=SUMPRODUCT(B2:B20,C2:C20)This is a compact alternative to adding a separate line-total formula for every row.
It works well for invoices, inventory values, and sales summaries.
2. Sum values that meet one condition
To add sales in C2:C100 only when the region in A2:A100 is East, compare the region range with the text criterion.
The comparison produces TRUE or FALSE values, which SUMPRODUCT treats as 1 or 0 in this calculation:
=SUMPRODUCT((A2:A100="East")*C2:C100)If the matching sales are 120, 80, and 50, the result is 250.
Keep the criteria range and sum range the same size.
If the criterion is in E2 instead, use (A2:A100=E2) so it can be changed without editing the formula.
3. Sum values that meet two conditions
To total sales for the East region during January, with regions in A2:A100, dates in B2:B100, and amounts in C2:C100, combine the tests:
=SUMPRODUCT((A2:A100="East")*(B2:B100>=DATE(2026,1,1))*(B2:B100<DATE(2026,2,1))*C2:C100)Using a start date inclusive and an end date exclusive includes every date and time in January.
The same pattern works with other criteria, such as product and sales representative.
Replace 2026 with a cell reference or suitable date inputs when you need a reusable report.
4. Count rows that meet multiple conditions
SUMPRODUCT can count records by multiplying condition tests.
To count rows where column A is East and column B contains a value greater than 100:
=SUMPRODUCT((A2:A100="East")*(B2:B100>100))Each row that meets both tests contributes 1; all other rows contribute 0.
For a case-insensitive text match, Excel and Google Sheets comparisons generally ignore letter case.
Use EXACT when case must matter, with attention to its array behavior in your spreadsheet version.
5. Add values that match one of several conditions
For an OR condition, add the tests and cap the result at 1 so a row matching both criteria is not counted twice.
To total amounts for either East or West:
=SUMPRODUCT(((A2:A100="East")+(A2:A100="West")>0)*C2:C100)The expression inside the comparison is greater than zero if either region matches.
This approach is useful for a short list of alternatives.
For many choices, a helper column or a function such as SUMIFS may be easier to maintain.
6. Calculate a weighted average
A weighted average gives more influence to values with larger weights.
If scores are in B2:B10 and their weights are in C2:C10, divide the sum of score-times-weight by the sum of the weights:
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)For scores of 80 and 90 with weights of 1 and 3, the result is 87.5.
If the weights can all be zero, prevent a divide-by-zero error with =IFERROR(SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10),0), or return a message instead of zero if that better fits the report.
7. Sum values between two dates
To total amounts in C2:C100 for dates in B2:B100 from March 1 through March 31, use an inclusive lower limit and exclusive upper limit:
=SUMPRODUCT((B2:B100>=DATE(2026,3,1))*(B2:B100<DATE(2026,4,1))*C2:C100)This also handles date-time values, because entries later in the day on March 31 remain earlier than April 1.
Make sure the date column contains real spreadsheet dates rather than text that only looks like dates.
When should you use SUMPRODUCT instead of SUMIFS?
SUMIFS is often simpler for straightforward conditional sums, especially with several criteria.
SUMPRODUCT is useful when the calculation combines multiplication, weighted values, OR logic, or tests that are awkward to express with SUMIFS.
For instance, the weighted-average formula uses SUMPRODUCT naturally.
Compare performance before using SUMPRODUCT over very large columns.
Bounded ranges such as A2:A5000 are generally preferable to full-column references in formulas that perform array calculations.
In newer Excel versions, SUMIFS or newer dynamic-array functions may make a calculation easier to read; use the function that keeps the workbook clear and compatible with its audience.
Excel and Google Sheets compatibility
SUMPRODUCT is available in modern desktop Excel, Excel for the web, and Google Sheets.
The examples use standard arithmetic, comparison tests, and DATE, which are supported in both spreadsheet applications.
The formula argument separator may be a semicolon instead of a comma, depending on regional settings.
For example, a localized installation may require =SUMPRODUCT(A2:A4;B2:B4).
The multiplication-based condition formulas shown here do not require a special array-entry key combination in current Excel or Google Sheets. If a formula behaves differently in an older spreadsheet version, check that version’s function support and the data types in the referenced ranges.
Common SUMPRODUCT errors and edge cases
- Range sizes differ: Every array should have the same number of rows and columns.
A mismatch can return an error or an unintended result. - Numbers are stored as text: Text-formatted quantities or amounts may not multiply as expected.
Convert them to numeric values before relying on the total. - Blank or text values appear in criteria ranges: Comparisons may still evaluate, but text in a numeric sum range can be ignored or handled differently across formula patterns.
Clean the input data and inspect unexpected totals. - Division by zero: A weighted average fails when its total weight is zero.
Use IFERROR or test the denominator explicitly. - Whole-column arrays slow a workbook: Large array calculations over entire columns can increase recalculation time.
Use ranges that cover the actual data or structured table references. - Parentheses are missing: Wrap each condition in parentheses before multiplying it by another condition or the values to sum.
This makes the intended logic easier to check.
When troubleshooting, verify a few rows by hand: confirm each condition, multiply the matched values, and add the results.
That small check can reveal a shifted range, text number, or boundary date that is difficult to spot in a long formula.