Sumproduct is a function in Excel that is a little tricky to get your head around, but can be really useful once you do.
Let’s look at an example, that might help us to see where and why we might want to use this function.
Look at the table below. We have the quantity sold and the price of each item.
We want to know the total sales. Now we could add a new column, and in that column we could calculate the sales amount, by doing Quantity X Price. And then we could add up that column, and that would give us the total sales figure. This would work, and in some circumstances would be what we would do. Especially if that column gave us useful information for each row that we wanted.
But, sometimes, we don’t need that row detail. We just need the total sales.
This is where we can use SUMPRODUCT, because in one formula it will do all the calculations for us!
We can see the SUMPRODUCT formula. It takes in the two columns of the table and returns just the one number, which it has worked out by multiplying each of the rows and then adding up the results just like we would have done by adding a new column with the Quantity X Price, and then summing it.
You can actually enter more than two arrays into the function. It will then multiply across each array and then sum the results.
Do note, that all the inputs need to be the same length, otherwise the function will return an error.