How to Use Sumproduct to Divide instead of Multiply

Sumproduct is powerful Excel formula to multiply 2 or more paired ranges (aka arrays or lists) together and sum the product.  This is most commonly used in areas like payroll where you have a list of people, their hours worked, and their pay rate.  If you wanted to get the total payroll for the period you have pulled up, you would use sumproduct([Range of Hours], [Range of Pay Rate]).

This formula is even more powerful as it can also handle division.  This is why there is no analogous divideproduct formula, because you can do it all with sumproduct!

The trick is to put 1/ in front of the range that will be your divisor (aka denominator). Here is a screenshot with an example:

dividebysumproduct

In essence, for each value in range D3:D6, Excel is transforming it into 1/D3 => 1/70, 1/D4 =>1/130, 1/D5 => 1/105, 1/D6 => 1/100 before performing the sumproduct between all 3 ranges.

There are a lot of situations you might need to use this where you are using rates, adjustment values, discounts, or percentages.  In the example above, I basically needed to adjust for the cost of living in different areas using rates to see the differences in cost of living if I were to move to different cities.  Basically, I needed to calculate a rate for each spending category to figure out the relative cost of living adjustment for each category.  I could have added another column to do that calculation and used that for a sumproduct, but this method results in a much cleaner looking spreadsheet.

Note this works in Google Doc’s version of sumproduct as well!  If other office programs don’t support the 1/[array] syntax, you’ll have to make an additional column to do the division and then use that new column in your sum product.

Here’s the demo spreadsheet that you can download and use to play around with:

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>