Here are a couple more great uses of SUMPRODUCT in Excel:
In the first example, I want to determine the sum of the sales for Tom and Ed.
I can do this in 3 steps, using the SUMIF function for each of them and then adding those results together:
However, using SUMIFS (since we have multiple criteria, we need to use SUMIFS rather than SUMIF), and using the SUMPRODUCT function, we can do it all in one step:
Notice how I wrapped the SUMIFS function with the SUMPRODUCT function. Since we have multiple criteria (H2 & H3), the SUMPRODUCT function allows us to analyze and process ranges of data at one time rather than in individual steps.
Here is another example. We have two columns of numbers and I want to subtract the two columns, then add up the differences. Again we can do this in multiple steps by subtracting each number in column J from column I then adding the results together:
But with SUMPRODUCT, we can do this in one step. Although SUMPRODUCT implies adding the product of number arrays, it can also add up the difference:
Finally, let’s take the difference between those two columns of numbers and multiply each by a percentage, then add them up:
Again, we’ve taken the difference between column I & J then multiplied each by the percentage in column K and added up the results. Let’s see how we can do this in one formula:
See how I’ve first subtracted the two columns I and J. Notice that I put those in parentheses because I wanted Excel to perform that operation first. In the normal order of operations, Excel will perform multiplication and division before addition and subtraction, but I wanted the subtraction first, so putting that in parentheses forces Excel to perform that operation first. Then by multiplying those results by the percentages, we get the same answer!