One of the great benefits of using excel is that it just seems like for almost anything that you are trying to do, there are several different ways to get them done. They might have small differences but in general they do come out to the same thing. When given the choice, it’s always better to use the “simpler”, lighter versions. Today, I’d like to look at the use of the “Sumproduct” excel function, which makes it easier to do two a combination of two things. First off, let’s take a look at a non-optimized spreadsheet that we would commonly use to calculate the value of a portfolio:
So how would you calculate the value of the portfolio? What most people would do is calculate the value of each position and add those up, see an example:
Certainly, that gets the job done as we do have the correct total of $54,785. However, if you add hundreds of lines, or even if you wanted to do it more easily, you could certainly do that. Instead of finding the total for each line and then summing up all of those, you could simply use the SumProduct function. Here is what it would look like in this case:
=SUMPRODUCT(C6:C11,D6:D11)
And the result:
As you can see, it gives me a much quicker and more flexible solution. I could easily add lines, modify numbers or do other modifications and then simply refer to that one formula to get the updated number. What does SumProduct do? Exactly what you would expect.
For each line included, it will multiply the numbers of each row (I included 2 rows here but you could add more) and sum all of those together. It’s simple yet brilliant.
As is always the case, you can download our spreadsheet here.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
