Microsoft Office Tutorials and References
In Depth Information
Summing Things Up
SUMPRODUCT works by first multiplying elements, by position, across the
ranges, then adding all the results. To see how this works, take a look at
the three ranges of values in Figure 8-6. I put letters in the ranges instead of
numbers to make this easier to explain. Suppose you entered the following
formula in the worksheet:
=SUMPRODUCT(B2:C4, E2:F4, H2:I4)
The result would be calculated by the following steps:
1. Multiplying A times H times N and saving the result.
2. Multiplying D times K times Q and saving the result.
3. Multiplying B times I times O and saving the result.
4. Multiplying E times L times R and saving the result.
5. Multiplying C times J times P and saving the result.
6. Multiplying F times M times S and saving the result.
7. Adding all six results to get the final answer.
Be careful when you’re using the SUMPRODUCT function. It’s easy to
mistakenly assume the function will add products of individual ranges. It won’t.
SUMPRODUCT returns the sums of products across positional elements.
As confusing as SUMPRODUCT seems, it actually has a sophisticated use.
Imagine you have a list of units sold by product and another list of the
products’ prices. You need to know total sales (that is, the sum of the amounts),
where an amount is units sold times the unit price.
In the old days of spreadsheets, you would use an additional column to
first multiply each unit sold figure by its price. Then you would sum those
intermediate values. Now, with SUMPRODUCT, the drudgery is over. The single
use of SUMPRODUCT gets the final answer in one step. Figure 8-7 shows how
one cell contains the needed grand total. No intermediate steps were necessary.