What is SUMPRODUCT in Excel?

SUMPRODUCT function is an effective and flexible tool that’s mainly utilised for summing the products of comparable ranges or arrays. This function takes the relevant elements from the supplied arrays, multiplies them, and then adds the results together.

Syntax :

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

• Array1 (required argument) – The first array argument, the elements of which you wish to multiply then add.
• Array2, Array 3 (optional argument) – This is the second (or third) array or range that we wish to multiply and then add.

Example 1: Simple Multiplication and Sum

Assume you have a list of products, unit prices, and quantities sold. You wish to calculate the overall sales revenue.

Data:

1. Product Names: A, B, C (in column A)
2. Unit Prices: 10, 20, 30 (in column B)
3. Quantities Sold: 5, 3, 2 (in column C)

A	B	C
Product	Unit Price	Quantity Sold
A	10	5
B	20	3
C	30	2

Using SUMPRODUCT:

To calculate total sales revenue, use the SUMPRODUCT function to multiply each unit price by the quantity sold, then add the results.

Formula:

=SUMPRODUCT(B2:B4, C2:C4)

Explanation:

• B2:B4 refers to the range of unit prices.
  
• C2:C4 refers to the range of quantities sold.
  
• The function multiplies each pair of corresponding elements from these ranges:

• 10 * 5 = 50
• 20 * 3 = 60
• 30 * 2 = 60

• It then sums these products: 50 + 60 + 60 = 170

Result:

The formula =SUMPRODUCT(B2:B4, C2:C4) will return 170, which is the total sales revenue.

Example 2: Conditional SUMPRODUCT

If you only want to calculate the total sales for products that meet a specified requirement (e.g., products in a specific category)

Syntax:

=SUMPRODUCT((C2:C5 = “SpecificCategory”) * A2:A5 * B2:B5)

Example:

Assume you have a database with several columns and want to determine total revenue by multiplying Unit Price and Quantity Sold just for Specific products.

Calculate the total revenue for “Electronics” category products using Unit Price and Quantity Sold.

Data:

A	B	C	D
Product	Category	Unit Price	Quantity
A	Electronics	10	5
B	Groceries	20	3
C	Electronics	30	2
D	Groceries	25	4
E	Electronics	40	1

1. Filter Data by Category:
   To filter by category, we’ll use the SUMPRODUCT and IF functions.
   
2. Formula:
   Enter the following formula in a cell to calculate the total revenue for “Electronics”:
   =SUMPRODUCT((B2:B6=”Electronics”)C2:C6D2:D6)
3. Explanation:

• (B2:B6=”Electronics”): This part generates an array of TRUE/FALSE values when the condition is met. Returns: {TRUE, FALSE, TRUE, FALSE, TRUE}.
  
• C2:C6: This is the Unit Price range.
  
• D2:D6: This is the Quantity Sold range.
  
• The SUMPRODUCT function multiplies only rows in the “Electronics” category, treating TRUE as 1 and FALSE as 0.
  
• For Product A: 10 * 5 = 50 (included)
  
• For Product C: 30 * 2 = 60 (included)
  
• For Product E: 40 * 1 = 40 (included)
  
• For Products B and D: Ignored because they are not “Electronics”.
  
• Sum of included products: 50 + 60 + 40 = 150

4. Result:

The formula =SUMPRODUCT((B2:B6=”Electronics”)C2:C6D2:D6) will return 150, which is the total revenue for “Electronics” products.

Using Excel to Filter Data:

To do this in Excel:

Enter Data:

Place the data in the respective columns as shown above.

Enter Formula:

• Click on a blank cell where you want the result to appear.
• Enter the formula: =SUMPRODUCT((B2:B6=”Electronics”)C2:C6D2:D6).

Press Enter:

Excel will calculate the total revenue for the “Electronics” category using the Unit Price and Quantity Sold.

Example 3: Weighted Average

To calculate a weighted average where weights are in column A and values in column B:

=SUMPRODUCT(A2:A5, B2:B5) / SUM(A2:A5)

• All arrays must have the same dimensions; otherwise, SUMPRODUCT will return an error.
  
• It can handle multiple arrays, but all arrays must be of equal length.

Example:

Calculate the weighted average price of the products based on the number of units sold.

Data:

A	B	C
Product	Price	Units Sold
P1	50	10
P2	75	150
P3	60	200
P4	90	50

1. Enter Data: Enter the data in an Excel sheet as shown above, starting from cell A1.
   
2. Formula: To calculate the weighted average price, use the following formula:=SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5)
   
3. Explanation:

• B2:B5 indicates the pricing range.
  
• C2:C5 indicates the range of units sold.
  
• SUMPRODUCT(B2:B5, C2:C5) multiplies each price by its corresponding units sold and then sums those products:
  
• 50 ∗ 100 = 5000
  
• 75 ∗ 150 = 11250
  
• 60 ∗ 200 = 12000
  
• 90 ∗ 50 = 4500
  
• Total = 5000 + 11250 + 12000 + 4500 = 32750
  
• SUM(C2:C5) sums the units sold:
  100 + 150 + 200 + 50 = 500
  
• To calculate the weighted average price, divide the sum of items by the total number of units sold.
  32750 / 500 = 65.5

Result:

The formula =SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5) will return 65.5, which is the weighted average price of the products.

Steps in Excel:

• Enter Data: Enter your data into an Excel spreadsheet exactly as shown in the table.
  
• Enter Formula:
  
  Click on a blank cell where you want the result to appear.
  
  Enter the formula: =SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5).
  
• Press Enter:
  
  Excel will calculate the weighted average price of the products and display the result in the selected cell.

Let’s consider a complex example :

Advanced Sales Analysis with Multiple Criteria

Dataset:

You have sales information for many products, including unit price, quantity sold, region, and sales quarter. You wish to look at the total revenue, weighted average pricing, and total revenue for a certain region and quarter.

Data:

A	B	C	D	E
Product	Unit Price	Quantity	Region	Quarter
P1	50	100	North	Q1
P2	75	150	South	Q1
P3	60	200	East	Q2
P4	90	50	West	Q2
P5	80	300	North	Q1
P6	70	180	South	Q3
P7	65	220	East	Q4
P8	85	90	West	Q3
P9	95	110	North	Q4
P10	55	170	South	Q4

• Calculate the total revenue.
  
• Calculate the weighted average unit price.
  
• Calculate the total revenue for the “North” region in “Q1”.
  
• Calculate the weighted average unit price for the “South” region in “Q4”.

Steps and Formulas

1. Calculate Total Revenue:

• Formula: =SUMPRODUCT(B2:B11, C2:C11)
  
• Explanation: This multiplies each unit price by the corresponding quantity sold and then sums the results.

2. Calculate Weighted Average Unit Price:

• Formula: =SUMPRODUCT(B2:B11, C2:C11) / SUM(C2:C11)
  
• Explanation: This divides the total revenue by the total quantity sold to get the weighted average unit price.

3. Calculate Total Revenue for the “North” Region in “Q1”:

• Formula: =SUMPRODUCT((D2:D11=”North”)(E2:E11=”Q1″)B2:B11*C2:C11)
  
• Explanation: This uses logical tests to include only the rows where the region is “North” and the quarter is “Q1”, and then multiplies the unit price by the quantity sold and sums the results.

4. Calculate Weighted Average Unit Price for the “South” Region in “Q4”:

• Formula:
  =SUMPRODUCT((D2:D11=”South”)(E2:E11=”Q4″)B2:B11C2:C11) / SUMPRODUCT((D2:D11=”South”)(E2:E11=”Q4″)*C2:C11)
  
• Explanation: This uses logical tests to include only the rows where the region is “South” and the quarter is “Q4”, then calculates the total revenue and divides it by the total quantity sold for these criteria.

Complete Example in Excel:

Data Entry:

Enter the data into the cells as shown in the table above.

Formulas and Results:

1. Total Revenue:

• Formula in Cell F2: =SUMPRODUCT(B2:B11, C2:C11)
  
• The formula calculates the total revenue.

2. Weighted Average Unit Price:

• Formula in Cell F3: =SUMPRODUCT(B2:B11, C2:C11) / SUM(C2:C11)
  
• The formula calculates the weighted average unit price.

3. Total Revenue for the “North” Region in “Q1”:

• Formula in Cell F4: =SUMPRODUCT((D2:D11=”North”)(E2:E11=”Q1″)B2:B11*C2:C11)
  
• The formula calculates the total revenue for the “North” region in “Q1”.

4. Weighted Average Unit Price for the “South” Region in “Q4”:

• Formula in Cell F5:
  =SUMPRODUCT((D2:D11=”South”)(E2:E11=”Q4″)B2:B11C2:C11) / SUMPRODUCT((D2:D11=”South”)(E2:E11=”Q4″)*C2:C11)
  
• The formula calculates the weighted average unit price for the “South” region in “Q4”.

Key Points to Remember

• SUMPRODUCT returns an error if all arrays have different dimensions.
  
• It can handle multiple arrays, but all arrays must be of equal length.

Test yourself with this example :

You have inventory data for various products, including their unit cost, quantity in stock, category, and storage location.

Data:

A	B	C	D	E
Product	Unit Cost	Quantity	Category	Location
P1	10	50	Electronics	Warehouse 1
P2	15	100	Furniture	Warehouse 2
P3	30	120	Electronics	Warehouse 3
P4	35	180	Furniture	Warehouse 1
P5	41	60	Electronics	Warehouse 2
P6	47	40	Furniture	Warehouse 3
P7	54	70	Electronics	Warehouse 1
P8	59	60	Furniture	Warehouse 2
P9	62	130	Electronics	Warehouse 3
P10	73	150	Furniture	Warehouse 1

1. Calculate the total inventory value.
   
2. Calculate the weighted average cost per item.
   
3. Calculate the total inventory value for the “Electronics” category in “Warehouse 1”.
   
4. Calculate the total inventory value for the “Furniture” category in “Warehouse 2”.
   
5. Calculate the weighted average cost per item for the “Electronics” category in “Warehouse 3”.
   
6. Calculate the weighted average cost per item for the “Furniture” category in “Warehouse 3”.

Conclusion:

To sum up, SUMPRODUCT is a superb example of effectiveness and style among Excel functions. Through the utilisation of its potential, you may exceed the limitations of conventional spreadsheet calculations and set off on a path to increased efficiency and understanding. So go ahead and use SUMPRODUCT to its full potential to transform your Excel experience right now!

Easy Guide to Excel’s INDIRECT Function #1

Understanding the SWITCH Function in Excel