In this tutorial we’ll explore how to use the SUMPRODUCT function to divide arrays and add them rather than multiplying those arrays.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a list of values and percentages. I’ve named the array of values as “Values” and the percentages as “Percentages”:
If I wanted to multiply the values times the percentages, then add the results, I could either do it manually the long way, or use SUMPRODUCT. To do it the long way, I’d enter a formula to multiply each value times its corresponding percentage, then add those values up using the SUM function, as so:
However, if I wanted to do that in one step, I would use SUMPRODUCT, with the following formula:
And I would get the same result:
But what if I need to find out the full number that, when multiplied by the percentage, gave me the value. In that case, I would need to divide the value by the percentage, then add those results, like this:
But how can we do that in one step using SUMPRODUCT? Easy! All you need to do is insert a “1/” in front of the array that you want to divide by, which would result in a formula like this:
And you will end up with the same reult:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!