Supercharge Your Marketing Strategy with Excel’s SUMPRODUCT #1

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)

ABC
ProductUnit PriceQuantity Sold
A105
B203
C302

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:

ABCD
ProductCategoryUnit PriceQuantity
AElectronics105
BGroceries203
CElectronics302
DGroceries254
EElectronics401

  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:

ABC
ProductPriceUnits Sold
P15010
P275150
P360200
P49050
  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:

ABCDE
ProductUnit PriceQuantityRegionQuarter
P150100NorthQ1
P275150SouthQ1
P360200EastQ2
P49050WestQ2
P580300NorthQ1
P670180SouthQ3
P765220EastQ4
P88590WestQ3
P995110NorthQ4
P1055170SouthQ4
  • 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:

ABCDE
ProductUnit CostQuantityCategoryLocation
P11050ElectronicsWarehouse 1
P215100FurnitureWarehouse 2
P330120ElectronicsWarehouse 3
P435180FurnitureWarehouse 1
P54160ElectronicsWarehouse 2
P64740FurnitureWarehouse 3
P75470ElectronicsWarehouse 1
P85960FurnitureWarehouse 2
P962130ElectronicsWarehouse 3
P1073150FurnitureWarehouse 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

ExcelMicrosoft

Recent Blogs

Mastering the Art of CSS Translate Property: A Comprehensive Guide with 6 examples

What is Translate Property in CSS? CSS translate property moves an element along the X and Y axes. Unlike other positioning attributes, such as position, translate does not disrupt the document’s natural flow, making it excellent for producing smooth animations and transitions. What is Transform property in CSS Visually appealing and dynamic user interfaces have […]

CSSHTMLJavascriptWeb design

CSS Rotate Property Explained: 5 Hands-On Examples for Web Developers

Understanding the CSS Rotate Property: The rotate property is part of the CSS transform module, which allows developers to apply different transformations to elements on a webpage. The rotate function allows you to rotate items by a specified angle, changing their orientation but not their location in the document flow. This characteristic provides tremendous versatility […]

CSSHTMLJavascriptWeb design

Web Storage API Essentials: Everything You Need to Know to Get Started – #1

Web Storage API Essentials – In today’s web development market, effective client-side data management is critical for providing seamless user experiences.

CSSHTMLJavascriptWeb Storage

Mastering Pattern Programs in C Programming: Explore 11 Examples

A fascinating introduction to c programming logic and aesthetics is provided by pattern programs. These programs bring life to the console by generating beautiful patterns that change from basic shapes to complex symmetrical masterpieces, all created through the manipulation of loops and conditional statements. This blog post takes you on a fascinating tour through the fascinating realm of C programming language pattern programs.

C programming