How to Use SUMPRODUCT in Excel

Introduction

SUMPRODUCT multiplies corresponding values across two or more ranges and returns the sum. It's one formula, but it handles total revenue calculations, conditional counting, conditional summing, and weighted averages without needing helper columns.

The Dataset

Here's a product sales table we'll use throughout:

	A	B	C	D
3	Product	Region	Units	Price
4	Laptop	East	15	800
5	Monitor	West	22	350
6	Keyboard	East	45	60
7	Laptop	West	10	800
8	Monitor	East	18	350
9	Keyboard	West	30	60

We want total revenue, conditional totals, and a weighted average price from this data.

How SUMPRODUCT Works

SUMPRODUCT takes two or more ranges of the same size, multiplies the values at each position, and adds up all the results. The syntax is:

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

• array1: The first range.
• array2: The second range (must be the same size as array1).

To calculate total revenue (Units x Price for every row):

=SUMPRODUCT(C4:C9, D4:D9)

Here's what happens at each position:

Product	Units	Price	Units x Price
Laptop	15	800	12,000
Monitor	22	350	7,700
Keyboard	45	60	2,700
Laptop	10	800	8,000
Monitor	18	350	6,300
Keyboard	30	60	1,800
		Total	38,500

The result is 38,500. Without SUMPRODUCT, you'd need a helper column to calculate each row's revenue and then SUM that column.

Conditional Counting

SUMPRODUCT can count rows that match a condition. When you write a comparison like B4:B9="East", Excel returns an array of TRUE and FALSE values. Multiplying by 1 converts them to 1s and 0s, and SUMPRODUCT adds them up.

To count how many rows are in the East region:

=SUMPRODUCT((B4:B9="East")*1)

The result is 3 (rows 4, 6, and 8).

To count rows where Units exceed 20:

=SUMPRODUCT((C4:C9>20)*1)

The result is 3 (Monitor at 22, Keyboard at 45, Keyboard at 30).

For simple AND conditions, COUNTIFS does the same job. SUMPRODUCT pulls ahead with OR logic. To count rows where the product is Laptop OR the region is West:

=SUMPRODUCT(((A4:A9="Laptop")+(B4:B9="West")>0)*1)

The result is 4. Here's why:

Product	Region	Laptop?	West?	Either?
Laptop	East	Yes	No	Yes
Monitor	West	No	Yes	Yes
Keyboard	East	No	No	No
Laptop	West	Yes	Yes	Yes
Monitor	East	No	No	No
Keyboard	West	No	Yes	Yes

The + acts as OR (if either condition is true, the sum is at least 1). COUNTIFS can only do AND. Getting OR with COUNTIFS requires multiple calls and subtraction to avoid double-counting. SUMPRODUCT handles it in one formula.

Conditional Summing

To sum revenue only for East region sales, multiply the condition array by the Units and Price arrays:

=SUMPRODUCT((B4:B9="East")*C4:C9*D4:D9)

Product	Region	East?	Units	Price	Included
Laptop	East	1	15	800	12,000
Monitor	West	0	22	350	0
Keyboard	East	1	45	60	2,700
Laptop	West	0	10	800	0
Monitor	East	1	18	350	6,300
Keyboard	West	0	30	60	0
				Total	21,000

The result is 21,000. The zeros knock out any row that isn't East.

For multiple conditions, stack them. Revenue for East Laptops only:

=SUMPRODUCT((A4:A9="Laptop")*(B4:B9="East")*C4:C9*D4:D9)

The result is 12,000 (only row 4 matches both conditions: 15 x 800).

SUMIFS can match the AND logic, but only when you're summing a single column. When the value you need comes from multiplying columns together (like Units x Price), SUMIFS can't do it directly. You'd need a helper column first.

Key takeaway: Use SUMIFS when you're summing a single column with AND conditions. Use SUMPRODUCT when the value comes from multiplying columns together, or when you need OR logic.

Weighted Averages

A regular average treats every value equally. A weighted average gives more influence to values with higher volume. SUMPRODUCT handles this with a simple pattern: SUMPRODUCT divided by SUM.

The weighted average price per unit sold:

=SUMPRODUCT(C4:C9, D4:D9) / SUM(C4:C9)

The numerator is total revenue (38,500). The denominator is total units (15 + 22 + 45 + 10 + 18 + 30 = 140). The result is 275.

Compare that to a simple average:

=AVERAGE(D4:D9)

Method	Formula	Result
Simple average	=AVERAGE(D4:D9)	403
Weighted average	=SUMPRODUCT(C4:C9, D4:D9) / SUM(C4:C9)	275

The simple average treats each product's price equally (800, 350, 60, 800, 350, 60). The weighted average reflects that Keyboards at $60 sell in much higher quantities than Laptops at $800, pulling the average down. For reporting actual revenue per unit, the weighted average is the accurate number.

Conclusion

SUMPRODUCT multiplies and sums across ranges in a single formula. For basic totals, it replaces helper columns. For conditional calculations, it handles OR logic and multi-column math that SUMIFS can't.

For simpler conditional sums, check out our guide on SUMIFS or COUNTIFS for conditional counting.