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.

Figure 8-6:

Following

the steps

used by

SUMPRODUCT.

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.