SUMPRODUCT multiplies corresponding values in two or more arrays and returns the sum of those products. It lets you calculate weighted totals and conditional sums without helper columns.
=SUMPRODUCT(array1, [array2], …)What does SUMPRODUCT do?
SUMPRODUCT is a Math & Trig function that does two jobs at once: it multiplies values in matching positions across arrays, then adds up all those products. For example, =SUMPRODUCT(A2:A4,B2:B4) calculates A2*B2 + A3*B3 + A4*B4 and returns a single total. It is the go-to function when you need a weighted total, such as quantity times price, without adding a helper column.
The function matters because it replaces long chains of multiplication and addition, and it can handle conditions directly. Office workers use it to total sales by region, calculate weighted averages, or count rows that meet several criteria. Students use it in statistics and finance assignments where weighted sums appear constantly.
A typical situation: you have a price list and an order list, and you need the total order value. Instead of creating a column of line totals, SUMPRODUCT multiplies each quantity by its price and sums the results in one formula. It also works when arrays are separated by other columns, as long as the ranges are the same size.
When to use SUMPRODUCT
- Calculating order value from quantity and unit price columns.
- Finding a weighted average, such as grades weighted by credits.
- Summing sales that match a region or product condition.
- Counting rows that meet two or more criteria at once.
- Computing total cost from hours worked and hourly rates.
- Combining percentages with base amounts, such as tax by category.
SUMPRODUCT syntax
=SUMPRODUCT(array1, [array2], …)| Argument | Required? | What to enter |
|---|---|---|
array1 | Required | The first range or list of numbers to multiply. Example: B2:B5 containing quantities. |
array2 | Optional | The second range or list, the same size as array1, whose values multiply the first array position by position. Example: C2:C5 containing prices. |
array3, … | Optional | Optional additional ranges of the same size. Every position is multiplied across all arrays, then all products are summed. |
What it returns: A single number: the sum of the products of corresponding values in the supplied arrays. If arrays have different sizes, it returns #VALUE!.
SUMPRODUCT examples
1 Sum sales for one region
The table lists sales by region, and the goal is total sales for the North region only. SUMPRODUCT compares A2:A6 with "North", producing 1 for matches and 0 otherwise. Multiplying that array by B2:B6 keeps only North sales, and the sum is 1250. The formula returns 1250 without a separate SUMIF.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 500 | 1250 | ||
| 3 | South | 700 | |||
| 4 | North | 750 | |||
| 5 | East | 600 | |||
| 6 | West | 400 | |||
| 7 |
2 Count rows with two conditions
The goal is to count how many orders are both in the North region and over 600. SUMPRODUCT builds two true/false arrays: region equals North, and sales greater than 600. Multiplying them gives 1 only where both are true, and the sum counts those rows. Only the North order of 750 qualifies, so the result is 1.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 500 | 1 | ||
| 3 | South | 700 | |||
| 4 | North | 750 | |||
| 5 | East | 600 | |||
| 6 | West | 400 | |||
| 7 |
3 Total cost from hours and rates by project
A small team logs hours and hourly rates for several projects. The goal is total labour cost for the Alpha project. SUMPRODUCT multiplies hours by rate for every row, then multiplies by 1 only where the project is Alpha. It calculates 10*50 + 8*45 for the two Alpha rows and ignores the rest. The returned total is 860.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Project | Hours | Rate | |||
| 2 | Alpha | 10 | 50 | 860 | ||
| 3 | Beta | 6 | 40 | |||
| 4 | Alpha | 8 | 45 | |||
| 5 | Gamma | 5 | 60 | |||
| 6 |
Common SUMPRODUCT errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The arrays have different numbers of rows or columns, so Excel cannot pair the values. | Make every range the same size, for example B2:B10 with C2:C10. |
| #VALUE! | One of the ranges contains text where a number is expected, and SUMPRODUCT cannot multiply it. | Check the ranges for text entries, blank cells with spaces, or error values and correct them. |
| Wrong total | A condition uses text without quotation marks, or the comparison is reversed. | Put text criteria in quotes, such as "North", and confirm the operator, such as > or =. |
| #N/A or unexpected 0 | Ranges include header rows or empty rows, or the criteria do not match exactly because of extra spaces. | Exclude headers from the ranges and clean trailing spaces with TRIM if needed. |
Tips for using SUMPRODUCT
- Use SUMPRODUCT when you need quantity times price across many rows and want to avoid a helper column.
- Keep all arrays the same size; mismatched ranges are the most common cause of errors.
- Wrap text criteria in double quotes, for example
"North", and use*to combine conditions. - For a weighted average, divide SUMPRODUCT by
SUMof the weights so the result stays on the original scale. - Remember that SUMPRODUCT treats TRUE and FALSE as 1 and 0 when you multiply them, which makes conditional sums easy.
- Test a conditional formula on a small range first to confirm the criteria match the values you expect.
Frequently asked questions
What is the difference between SUMPRODUCT and SUM?
SUM adds values in one range. SUMPRODUCT multiplies matching values across two or more ranges first, then adds the products, so it handles weighted totals in one step.
Can SUMPRODUCT handle conditions?
Yes. Multiply the number array by a true/false array such as (A2:A10="North"). The condition becomes 1 for matches and 0 otherwise, so only matching values are included.
Why does SUMPRODUCT return #VALUE!?
The most common reason is that the ranges are different sizes. Make every array the same number of rows and columns, and check that no text sits in a numeric range.
Does SUMPRODUCT work with dates?
Yes, if the dates are real Excel dates. You can compare them with date criteria, such as (A2:A10>=DATE(2024,1,1)), and multiply by an amount column.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. SUMPRODUCT reference