Thursday, July 7, 2011

Use Excel's SUMPRODUCT To Summarize Worksheet Data

SUMPRODUCT


What Does It Do ?

This function uses at least two columns of values. The values in the first column are multipled with the corresponding value in the second column. The total of all the values is the result of the calculation.

Syntax

=SUMPRODUCT(Range1, Range, Range3 through to Range30)

Formatting
No special formatting is needed.

Example

The following table was used by a drinks merchant to keep track of stock.
The merchant needed to know the total purchase value of the stock, and the potential value of the stock when it is sold, takinging into account the markup percentage.

The =SUMPRODUCT() function in Excel is used to multiply the Cases In Stock with the Case Price to calculate what the merchant spent in buying the stock.

The =SUMPRODUCT() function in Excel is used to multiply the Cases In Stock with the Bottles In Case and the Bottle Setting Price, to calculate the potential value of the stock if it is all sold.





Twitter Bird Gadget