MrExcel.com - Your One Stop for Excel Tips & Solutions

Introducing The Boring Use Of SUMPRODUCT

February 01, 2021 - by

Introducing The Boring Use Of SUMPRODUCT

Challenge: Your IT department sends you a file 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.

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 finds 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, 0 5 6, 12 9 . 8 0.

Figure 13. SUMPRODUCT can do many intermediate multiplications and sum the results.
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.

Title Photo: Melissa Van Gogh at Unsplash.com

This article is an excerpt from Excel Gurus Gone Wild.