Microsoft Office Tutorials and References

In Depth Information

**INTRODUCING THE BORING USE OF SUMPRODUCT**

INTRODUCING THE BORING

USE OF SUMPRODUCT

Challenge:
Your IT department sends you a ﬁ le with unit price and quantity

sold. You need to calculate total revenue. You usually add a new column and

total that column, but there must be a way to total the 5,000 line items with only

one formula.

Part

I

Solution:
There is an Excel function designed to solve this very problem. The

SUMPRODUCT
function takes two or more similar-shaped ranges, multiplies

them together, and sums the results.

In Figure 13, the range
C4:C5003
contains quantities. Cells D4:D5003 contain

unit prices. The formula
=SUMPRODUCT(C4:C5003,D4:D5003)
performs

5,000 multiplications and adds up the results. For example, Excel ﬁ nds that

C4*D4
is
57,473.95,
and
C5*D5
is
31,488.30
. This process continues for

all the cells in the array. Finally, Excel sums the individual multiplication results

and returns the answer
181,056,129.80
.

Figure 13.
SUMPRODUCT
can do many intermediate

multiplications and sum the results.

Additional Details:
You can specify up to 30 similar-shaped arrays as

arguments for
SUMPRODUCT
(255 arrays in Excel 2007)

Summary:
SUMPRODUCT
can multiply two or more arrays and sum the

results.