SUMPRODUCT

Posted on 18-01-2020 , by: admin , in , 0 Comments

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.

Item Sold price
Tyres 5 100
Filters 2 10
Bulbs 3 2
Total Sales Value : 526  =SUMPRODUCT(D4:D6,E4:E6)

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 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 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.

Pro-duct Ca-ses In Sto-ck Case Price Bott-les In Case Bott- le Cost Mar-kup Bott- le Sell- ing Price
Red Wine 10 £120 10 £12. 00 25% £15. 00
White Wine 8 £130 10 £13. 00 25% £16. 25
Cham- pagne 5 £200 6 £33. 33 80% £60. 00
Beer 50 £24 12 £2. 00 20% £2. 40
Lager 100 £30 12 £2. 50 25% £3. 13
=D39 / E39 =F39+ F39 *G39
Total Value Of Stock : £7,440  =SUMP- RODUCT (C35:C39 D35:D39)
Total Selling Price Of Stock : £9,790  =SUMP RODUCT (C35:C39, E35:E39, zH35:H39)
Profit :  £2,350  =E44-E43

Leave a comment