Tuesday, October 19, 2004

Excel Formula SUMPRODUCT

The SUMPRODUCT formula is a great way to calculate proportional totals.

The simplest way of using it is with an example of qty and value of stock items.

One way to find the total inventory value is by creating the last column that totals each individual row and is then summed. (Column D in the example below)

The alternative way is to create a SUMPRODUCT formula. The syntax is =SUMPRODUCT(range1, range2). See the formula in Cell C6 in the picture.
This adds the cumulative total of each row in the range multiplied by the second row. In this case that would return a result of $11.97. This would obviate the need for Column D.

SUMPRODUCT works both horizontally and vertically.