How to use SUMPRODUCT

The SUMPRODUCT formula allows you to write compact formulae. However, it can also result in a lack of transparency, and increases the risk of introducing errors into your spreadsheets.

One useful application of the SUMPRODUCT formula is to allow you to quickly calculate revenue, as well as to produce reports where revenue may be aggregated according to different conditions.

SUMPRODUCT example 1: Calculating revenue

A useful application of the SUMPRODUCT formula is to calculate total revenue. For example, assume we are given the below data about the quantity sold and the unit selling price:

To calculate total revenue using the SUMPRODUCT Formula:

• Name the range F11:F25 as “Quantity”, and range G11:G25 as “Price”
• Select cell E29 and type in the formula: =SUMPRODUCT(F11:F25,G11:G25) or =SUMPRODUCT(Quantity, Price)
• Note that we could have used an asterisk instead of the comma: =SUMPRODUCT((F11:F25)*(G11:G25)) or =SUMPRODUCT(Quantity*Price)

In this example, either notation works well, but in more complicated examples involving conditions (see Example 3), it is preferred to use the asterisk.

SUMPRODUCT example 2: Calculating revenue with varying product prices

The first example was relatively simple since, for each product, we had a single value for the quantity and a single value for the price, i.e. the price is constant.

But what happens in cases where firms change product prices? Consider Product P (below) where the unit price of Product P is $5.11 in Year 1, $4.77 in Year 2, and $4.15 in Year 3. Also, we can see that 146 units of Product P were sold in Year 1, 115 units in Year 2, and 113 units in Year 3.

Let’s calculate the total revenue (over all the years and all products):

• Select cell E57 and type in the following formula: =SUMPRODUCT($F$42:$H$46,$F$49:$H$53)
• Notice that the range $F$42:$H$46 shows the product quantities across different years, whilst the range $F$49:$H$53 shows the product prices across different years.
• The effect of the SUMPRODUCT formula is to multiply quantities with corresponding prices, for example, it multiplies 146 by $5.11, 115 by $4.77, 113 by $4.15, 148 by $3.11, and so on. Then, it adds these results to determine the total revenue.

SUMPRODUCT example 3: Data aggregation and grouped reporting

In the previous two examples, we have used the SUMPRODUCT formula to calculate total revenue.

Now, we’ll see how we can use the SUMPRODUCT formula with conditions to calculate revenue by product, by salesperson, etc.

In this screenshot, each row indicates a separate transaction). For example, in this first transaction, Mary sold 646 units of Product A at a price of $75.8 per unit.

Before we continue, we’ll use the following named ranges:

• F76:F93 is named as “Products”
• G76:G93 is named as “Salespeople”
• H76:H93 is named as “Quantities”
• I76:I93 is named as “Prices”
• J76:J93 is named as “Revenues”

Here, our task is to produce a summary report showing the revenue by product, i.e. the total revenue for Product A, total revenue for Product B, total revenue for Product C, total Revenue for Product D.

In order to calculate the total revenue for Product A:

• Select cell E108 and type this formula: =SUMPRODUCT((Products=C108)*Revenues)
• This helps us to calculate total revenue for the Product that is specified in cell C108 (which is Product A). The same procedure can also be used to break down revenue by sales people, and by month (see example workbook).

An even more interesting and powerful application of SUMPRODUCT is when we would like to split revenue by Product type and by Salesperson (to analyse the revenue obtained by different salespeople for different products):

In order to calculate the total revenue for Product A where Mary sold units of Product A:

• Select cell F125 and type in the formula: =SUMPRODUCT((Products=F$124)*(Salespeople=$E125)*Revenues)
• This allows us to calculate revenue for where the product type is specified in cell F$124 (i.e. Product A) and the salesperson is specified in cell $E125 (i.e. Mary). A similar formula was used across other cells.

SUMPRODUCT - Advantages and Disadvantages

The advantages of the SUMPRODUCT formula are that it allows you to write very concise formulae with multiple conditions. It is calculated faster than the corresponding SUMIF, or {SUM(IF(..))} formulae.

The disadvantages are that it can result in a lack of transparency, increasing spreadsheet errors.

COMMENTS