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,056,129.80.
 |
| Figure 13. SUMPRODUCTcan 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.
megjegyzés0
Post a Comment