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
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