SUMPRODUCT Function (Functions in Excel)
Item | Sold | price | |
Tyres | 5 | 100 | |
Filters | 2 | 10 | |
Bulbs | 3 | 2 | |
Total Sales Value : | 526 | =SUMPRODUCT(D4:D6,E4:E6) |
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 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. |
Product | Cases In Stock | Case Price | Bottles In Case | Bottle Cost | Markup | Bottle Selling Price |
Red Wine | 10 | £120 | 10 | £12.00 | 25% | £15.00 |
White Wine | 8 | £130 | 10 | £13.00 | 25% | £16.25 |
Champagne | 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 | =SUMPRODUCT(C35:C39,D35:D39) |
Total Selling Price Of Stock : | £9,790 | =SUMPRODUCT(C35:C39,E35:E39,H35:H39) |
Profit : | £2,350 | =E44-E43 |
0 comments:
Post a Comment