Tackle Two Operations with one Function in Microsoft Excel

Posted by Dan St. Hilaire  /  October 7, 2014  /  Excel   —   No Comments ↓

In this blog post we will show you a very handy mathematical function that performs two operations in one, multiplication and addition. Using =SUMPRODUCT() allows you to return the sum of the products of corresponding ranges or arrays. In our example below we show you how to use the SUMPRODUCT Function to get a grand total. Our Video below shows you how you may currently be calculating your grand total, and we also show you exactly, step-by-step how to use this function.

Calculating Total Revenue

The spreadsheet snip below lists electronic products in column A. Column B records the sales volumes (Units sold) and column C displays the products price in dollars. Our objective is to calculate the total revenue from these sales.10032014_01

 

 

 

 

 

 

 

We could add, in column D, calculations of revenue for each product using a standard formula of =B3*C3 and dragging down to the formula and then using the SUM Function as shown below.

10032014_02

 

 

 

 

Or we could use the SUMPRODUCT function to perform the multiplication and addition in one step:

10032014_03

 

 

 

 

 

 

The SUMPRODUCT function takes two (or more) ranges of cells, calculates the product of each corresponding pair of cells, and then adds these products together. So in this example the SUMPRODUCT formula is equivalent to: =B3*C3+B4*C4+B5*C5+B6*C6+B7*C7

Watch the video below to see step-by-step how you can use the SUMPRODUCT Function.



Topics: Excel