Function SUMPRODUCT:

Now it`s time to understand the secrets of the function SUMPRODUCT. This function can be adapted to many different scenarios to suit your needs. Let’s check a few of them.

SUMPRODUCT – Basics

In the example 1 below, let`s understand how Excel users normally calculate total revenues (i.e. sum of the results coming from multiplication of the arrays Price and Volume) against how it can be done with the function SUMPRODUCT.

Calculating Revenues with a Helper Column (Example 1)

In Example 1 below, we have different product volumes and the price for each product unit. What we want to do is calculate the total revenue based on this data.

First, let`s get that result with the function SUM. Follow these steps:

1. Insert a new column on the right side of the array “Price”. This will be our helper column. Let’s label it “Revenues ($)”.

2. Go to cell D5 and insert the equal sign. Then, type B5, which represents the volume number, and multiply it by C5, which represents the price per unit.

3. Press ENTER and the result is $6,000.

Expand the function to the rest of the rows to get the remaining results.

Now, let’s work with the SUM function:

1. Go to cell D11 and insert the function SUM.

2. Select the entire array of the helper column, from D5 to D10, as the range argument of this function.

3. Press ENTER and we can finally get to the total revenue which is $75,000.

Calculating Revenues with the function SUMPRODUCT (Example 1)

However, we don’t have to go through all these steps to get the same results. That’s what the SUMPRODUCT function is for, so let’s try it:

1. Go to cell C11 and insert the SUMPRODUCT function.

2. The argument “array 1” will be the entire array of volume, from B5 to B9.

3. Then, the argument “array 2” will be the entire array of price per unit, from C5 to C9.

4. Close brackets, press ENTER, and we get the same result as before, which is $75,000.

Essentially, the SUMPRODUCT function makes all the individual multiplications and then adds the result which saves a lot of your time. This is the standard use of the function SUMPRODUCT and quite a few Excel users are familiar with it. Now, let’s focus on a few secrets most Excel users don’t know about!

Secrets of the function SUMPRODUCT

With the basics of the function SUMPRODUCT covered, let`s now check out 3 secrets of this important function.

Secret # 1: Incomplete Dataset (Example 1 as a reference)

What happens to our calculations when we’re missing a data input? Continuing with example 1, let’s say that the volume number in the first row is not available. So, let’s write “not available” in cell B5, press ENTER, and look at what happens to the two functions:

As you can see, the function SUM loses track, while the function SUMPRODUCT automatically adapts. That’s because SUMPRODUCT treats “text values” as 0, and this is the key difference between these functions.

Thanks to this quick adaptation, the function SUMPRODUCT is treating the text “Not Available” as 0 and automatically reducing the revenue calculation by $6,000. Therefore, this function is much more stable than the SUM function. With this concept in mind you can delete the entire helper column as you no longer need it.

Secret # 2: Performing calculations with ranges of different dimensions (Example 2 as a reference)

The function SUMPRODUCT is also very useful when you need to work with ranges of different dimensions, so let’s practice it. In Example 2, we are working with two products, A and B. Product A costs $ 60 per unit and Product B costs $ 40 per unit.

The volume to be sold for both products is addressed in range G7 to H9 and, as you can see, we have 3 probability scenarios for these volumes, which are Worst Case at 10%, Base Case at 70%, and Best Case at 20%. What we need to calculate here is the expected revenue based on these scenarios.

Excel users can solve a problem like this either through a side calculation or through SUMPRODUCT. Let`s check each of them.

Side Calculation: 

Normally, Excel users would make individual calculations and then use the SUM function as such:

1. Go to cell G13 and multiply G7, which is the volume of Product A, by G4, which is the price of each unit.

2. Multiply again by F7, which is the respective probability for this scenario.

3. Press ENTER and the result is $6,000 of revenue, which is the worst-case scenario for Product A.

The issue here is that we can’t just expand the SUM function to obtain the rest of the results. Instead, we must waste time adjusting the formula for absolute x relative reference as per image below:

Only then we are able to get the correct range to perform the sum calculation. After we’re done with all that, we can use the SUM function to add them all up in cell H16 for instance and the total expected revenue will be $163,000.

SUMPRODUCT to tackle different range dimensions: 

However, we can also save ourselves the trouble of going through all those steps by using the SUMPRODUCT function. We can then delete all these side calculations and simply follow the below:

1. Go to cell H11 and insert the SUMPRODUCT function.

2. For the “array 1” argument, select the entire probability array, from F7 to F9.

3. Then, we multiply it (*) by the array of prices for Products A and B, which is the range G4 to H4.

4. Finally, multiply it (*) by the array of volume for both products, which is the range G7 to H9.

5. Close brackets, press ENTER, and we will get the exact same result as before but in just a few seconds.

Secret # 3: Replacing the function COUNTIF by the function SUMPRODUCT (Example 3 as a reference)

Something to keep in mind about the SUMPRODUCT function is that it can also be used to identify a specific data point in an array. In Example 3, we have a range of products and specific criteria. We want to count how many products of each type there are in this range.

To do that, Excel users would normally work with the function COUNTIF. So, let`s quickly calculate it and then compare it with the function SUMPRODUCT.

Using the Function COUNTIF

Follow the steps below:

1. Go to cell J11 and insert the COUNTIF function.

2. The “range” argument is the entire array of products, from J5 to J9.

3. The “criteria” argument is L5.

4. Press ENTER and the result is 2, which is the number of times product A is found in the range we selected.

Now, let`s get the same result with the function SUMPRODUCT.

Using the Function SUMPRODUCT

Follow the steps below:

1. Go to cell M5 and insert the SUMPRODUCT function.

2. The “array 1” argument will be J5 to J9.

3. Then, type the equal sign and add the “array 2” and add L5 which the criteria we need.

4. Close brackets, press ENTER, and the result is 0 at this stage.

So, why do we get 0 instead of 2? Because, as we saw in Example 1, the function SUMPRODUCT treats “text values” as 0, and that’s exactly what’s happening here.

If we go back to the function in cell M5, select the argument “array 1”, and press F9, Excel will confirm these are basically a sequence or an array of text values, TRUE and FALSE. That’s why, for now, the result is 0.

However, with the correct approach, Excel can basically treat these values as 1 and 0. If you need to refresh your memory about this concept, revisit the INDEX & MATCH tutorial (Part III).

Adapting the Function SUMPRODUCT

What we have to do to get the correct result here is turn these “text values” into numerical values by adding a mathematical operation to the SUMPRODUCT function. The mathematical operation needs to be neutral, meaning it can’t interfere with the logic of the calculation we already have.

Here’s what we can do:

1. Go back to the function in cell M5 and insert another pair of brackets to isolate the argument “array 1”.

2.Then, multiply the new pair of brackets by 1, press ENTER, and we now get the correct result which is 2.

This is one way of performing a neutral mathematical operation, because multiplying a number by 1 does not change the logic of the calculation and therefore the final result is still the same. Now instead of multiplying by 1, we could also have divided it by 1 or add 0 and we would also get the correct result. However, to make things even easier and faster, you can simply insert the minus sign (-) twice at the beginning of those brackets. This is very helpful when you’re working with hundreds of calculations like this in your spreadsheet.

Finally, if we audit this formula again, we can then confirm Excel has now converted the sequence of TRUE and FALSE into a sequence of 1s and 0s, and therefore it identified 2 values that meet this criterion.

Now, whenever we select another product in this drop-down list, the correct result will be returned by this SUMPRODUCT function.