SUMPRODUCT Generator
Multiply matching cells in two ranges and add all the products in one cell.
SUMPRODUCT
Multiply matching cells in two ranges and add all the products in one cell.
First set of numbers, for example quantity per row.
Second set of numbers, for example unit price. Must be exactly the same size as the first range.
=SUMPRODUCT(B2:B100, C2:C100)Worked Example
An invoice where column B holds quantities and column C holds unit prices for rows 2 through 100.
=SUMPRODUCT(B2:B100, C2:C100)Returns: Returns B2*C2 + B3*C3 + ... + B100*C100 as a single number - the invoice total, with no extra line-total column needed.
Checks Before You Paste
- •The ranges must be identical in shape. SUMPRODUCT(B2:B100, C2:C101) returns #VALUE!, and so does mixing a whole-column reference like B:B with a bounded range.
- •Comparisons return TRUE and FALSE, which Excel's SUMPRODUCT treats as non-numeric and effectively counts as zero. Coerce them to 1 and 0 with a double minus or by multiplying: =SUMPRODUCT(--(A2:A100="West"), C2:C100) or =SUMPRODUCT((A2:A100="West")*(C2:C100)). Both forms behave correctly in Google Sheets too.
- •Text and blanks inside a range are treated as zero rather than raising an error, but a real error value such as #N/A anywhere in the range propagates and takes the whole result with it. Clean the data or wrap the offending range in IFERROR, as in =SUMPRODUCT(IFERROR(B2:B100, 0), C2:C100).
How SUMPRODUCT works
Availability: Every version of Excel since 1993, plus Excel for Mac, Excel for the web, Google Sheets and LibreOffice Calc. It needs no array confirmation and no modern build.
SUMPRODUCT pairs up the cells of two or more equally shaped ranges, multiplies each pair, and adds the products. On an invoice with quantities in one column and unit prices in the next, that single call replaces a line-total column and a SUM beneath it. Given only one array it simply adds it, which makes SUMPRODUCT(range) a slower spelling of SUM(range) and is not the reason to use it.
The reason to use it is that it evaluates arrays natively, without Ctrl+Shift+Enter, in every version of Excel ever shipped. A comparison such as (A2:A500="West") produces an array of TRUE and FALSE the same length as the range, and multiplying two of those arrays together gives 1 only where both held - so SUMPRODUCT does conditional counting and conditional totalling that predate SUMIFS and still reach places SUMIFS cannot. It can apply a function to a whole range, as in MONTH(A2:A500), it can be case-sensitive through EXACT, it can express OR with addition, and unlike SUMIF and COUNTIF it reads a workbook that is closed.
Booleans have to be coerced to numbers first. SUMPRODUCT treats TRUE and FALSE as non-numeric and counts them as zero, so =SUMPRODUCT((A2:A500="West")) returns 0 rather than a count. Two ways out: a double unary minus, --(A2:A500="West"), or multiplication, which coerces as a side effect. Multiplying is how AND is written - (A2:A500="West")*(B2:B500>100) - and adding is how OR is written, with the sum wrapped in a >0 test so a row satisfying both conditions still only counts once.
The choice between commas and asterisks between arrays is not cosmetic. Commas make SUMPRODUCT tolerant: text and blank cells inside a range are read as zero and the calculation continues. Asterisks make it strict: multiplying by a text cell raises #VALUE! and takes the whole result with it. Either way a real error value such as #N/A anywhere in any range propagates, which is why a SUMPRODUCT over a column of lookups usually needs IFERROR around the offending range.
Syntax
=SUMPRODUCT(array1, [array2], ...)| Argument | Required | What it does |
|---|---|---|
array1 | Required | The first range or array. May be two-dimensional. Given alone, SUMPRODUCT returns its total rather than a product. |
array2 | Optional | Up to 254 further arrays, every one identical in height and width to the first. A different shape returns #VALUE!, and a whole-column reference cannot be paired with a bounded range. |
More worked examples
Module marks in B2:B12 with credit weightings in C2:C12, and you want the weighted average rather than the plain mean.
=SUMPRODUCT(B2:B12, C2:C12)/SUM(C2:C12)Returns: The credit-weighted average mark. It differs from AVERAGE(B2:B12) whenever the credits are not all equal, because a heavier module moves the figure further.
Dividing by SUM of the weights is what makes it an average rather than a total. Leave it off and you get a number that grows with the weights.
A transactions sheet with dates in A2:A5000 and amounts in D2:D5000, totalling March 2026 without adding a month column.
=SUMPRODUCT((MONTH(A2:A5000)=3)*(YEAR(A2:A5000)=2026)*D2:D5000)Returns: The total of every March 2026 amount.
SUMIFS cannot do this directly - it compares cells against a criterion and cannot run MONTH over a range. It can reach the same answer with a date window, ">="&DATE(2026,3,1) and "<"&DATE(2026,4,1), and will be faster on a large sheet.
Regions in A2:A500, counting the rows belonging to either West or North.
=SUMPRODUCT(--((A2:A500="West")+(A2:A500="North")>0))Returns: The number of rows in either region, each counted once.
Addition is OR. The >0 test matters when the two conditions can both be true of one row - without it, addition would count that row twice.
Common mistakes
- Ranges of different shapes
- Every array must be the same height and width. SUMPRODUCT(B2:B100, C2:C101) returns #VALUE!, and so does pairing B:B with C2:C100. This is the most common failure and it does at least announce itself.
- A comparison that returns 0
- =SUMPRODUCT((A2:A500="West")) gives 0, not a count, because TRUE and FALSE are not numbers to SUMPRODUCT. Coerce them: --(A2:A500="West") or multiply the condition by another array.
- Text in a range behaving differently with * than with ,
- Commas treat text and blanks as zero and carry on; asterisks multiply by them and return #VALUE!. A formula that worked until somebody typed "n/a" into the amounts column was almost certainly using asterisks.
- One error value poisoning the whole result
- A single #N/A from a lookup anywhere in any of the arrays makes the entire SUMPRODUCT #N/A. Wrap the range - =SUMPRODUCT(IFERROR(B2:B500,0), C2:C500) - or fix the source column, which is usually the better answer.
- Whole-column references making it crawl
- SUMPRODUCT genuinely evaluates every cell it is given, with none of the used-range shortcuts SUMIFS enjoys. A:A means a million rows per array, per recalculation. Bound the ranges, or reference an Excel Table column that sizes itself.
Frequently Asked Questions
Because a comparison produces TRUE and FALSE, and SUMPRODUCT ignores non-numeric values. The first minus turns TRUE into -1, the second turns it back into 1, and FALSE becomes 0 either way. Multiplying by another array does the same coercion, which is why -- only appears when a condition stands alone.
When the condition is a function applied across a range, such as MONTH or WEEKDAY or LEN; when it has to be case-sensitive, through EXACT; when the logic is OR rather than AND; when the source workbook is closed, which SUMIF and COUNTIF refuse; and when the arrays are calculated rather than sitting on a sheet.
No. SUMPRODUCT handles arrays natively in every version, which is exactly why it became the standard workaround before dynamic arrays existed. Confirming it with Ctrl+Shift+Enter is harmless but changes nothing.
Multiply for AND - (A="West")*(B>100) is 1 only where both hold - and add for OR, wrapping the sum in a >0 test so a row meeting both conditions is not counted twice. The AND and OR functions themselves collapse a whole array to one value and are no use inside SUMPRODUCT.
Yes, and that is the normal use. Each condition produces one value per row, and multiplying them aligns the tests position by position, so =SUMPRODUCT((A2:A500="West")*(B2:B500>100)) counts records meeting both - the same answer COUNTIFS gives, in a form that also works on closed files.