SUMPRODUCT function in Excel

SUMPRODUCT multiplies ranges together item by item and adds up the results.

SUMPRODUCT does in one cell what would otherwise need a helper column. To value your stock you would normally add a column of price times quantity and total it. SUMPRODUCT multiplies each pair and adds the answers without the extra column.

Syntax

=SUMPRODUCT(array1, [array2], ...)

Argument	What it is
`array1`	The first range.
`array2, ...`	More ranges of the same size. Matching items are multiplied.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: A small price list with item codes. The table is sorted by code.

	A	B	C	D	E
1	Code	Item	Category	Price	Stock
2	A101	Basmati Rice 5 kg	Grocery	650	40
3	A102	Toor Dal 1 kg	Grocery	160	75
4	B201	Notebook	Stationery	60	300
5	B202	Ball Pen	Stationery	10	0
6	C301	LED Bulb	Electronics	110	200
7	C302	Power Bank	Electronics	1299	18
8	D401	Steel Bottle	Home	450	55

=SUMPRODUCT(D2:D8,E2:E8)
Result: 126132
Price times stock for each row, all added up: the value of the stock.
=SUMPRODUCT((C2:C8="Grocery")*D2:D8*E2:E8)
Result: 38000
A condition inside: the stock value of Grocery items only.
=SUMPRODUCT((C2:C8="Grocery")*1)
Result: 2
Counting with a condition: how many rows are Grocery.
=SUMPRODUCT((E2:E8>0)*(D2:D8>100))
Result: 5
Two conditions multiplied: items in stock that cost more than 100.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=SUMPRODUCT((MONTH(A2:A13)=3)*F2:F13)
Result: 5250
Something SUMIFS cannot do without a helper column: total for the month of March.
=SUMPRODUCT(E2:E13,F2:F13)/SUM(E2:E13)
Result: 1034.431138
A weighted average: the average amount, weighted by units.

How it works

Give it two ranges of the same size. It multiplies the first items together, the second items together, and so on, then adds all those products. With three ranges it multiplies three numbers on each row.

The ranges must have the same number of rows and columns. If they differ, the result is #VALUE!. Text in a range counts as zero.

Conditions inside SUMPRODUCT

The real power comes from putting a test inside. A test such as C2:C8="Grocery" produces a list of TRUE and FALSE values. When that list is multiplied by something, TRUE acts as 1 and FALSE as 0. So

=SUMPRODUCT((C2:C8="Grocery")*D2:D8*E2:E8)

keeps the Grocery rows and turns every other row into zero before adding.

Multiply two tests for and: (test1)*(test2).
Add two tests for or: ((test1)+(test2)>0).
A test on its own must still be turned into numbers. Multiply it by 1, as in the third example.

Each test needs its own pair of brackets.

What it can do that SUMIFS cannot

SUMIFS is faster and easier to read, and should be the first choice. SUMPRODUCT is for the cases SUMIFS cannot handle:

a condition on a calculation, such as the month of a date, the first letter of a name or the length of a code;
"or" conditions without writing several formulas;
a weighted average, as in the last example;
workbooks that must work in very old versions of Excel.

Weighted averages

A plain average treats every row alike. When rows stand for different quantities, weight them: multiply each value by its weight, add up, and divide by the total weight. A student's result where subjects carry different credits, or the average price paid across purchases of different sizes, are both SUMPRODUCT(values, weights) / SUM(weights).

Speed

SUMPRODUCT works through every cell of every range you give it. Whole-column references such as A:A make it process over a million rows and can slow a workbook badly. Give it the real extent of the data, or convert the data to a table so that the ranges grow by themselves.

Common mistakes

Ranges of different sizes, giving #VALUE!.
Commas where stars are needed. =SUMPRODUCT(C2:C8="Grocery",D2:D8) returns 0, because TRUE and FALSE separated by a comma are not turned into numbers. Use *.
Text in a column being multiplied with *, which gives #VALUE!. Keep headings out of the ranges.

In Google Sheets

SUMPRODUCT works the same way in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.