If cell contains a value, multiply...
I have a speadsheet containing customer order details. If a customer orders
10 of one item and 5 of another item, I enter 10 in the column of the first item, and 5 in the column of the second item...etc I would like to be able to work out the total cost of the items in another column automatically. Normally I would do £4.50 x 10 if a customer wanted 10 of an item, and so on manually until I had their grand total. Is there anyway I can do this automatically? Something like: SUM (IF cell A1 contains any value multiply by £4.50 (IF B1 contains any value multiply by £7.50...)) and so on across the speadsheet. Could I also put the item costs elsewhere in the speadsheet to make it easier it update price changes? Many thanks for any suggestions/help :) Jess |
If cell contains a value, multiply...
Try the formula with your data placed in the below manner
Row 1 contains the product names Row 2 the corresponding rates Row 3; try entering the quantities Col A Col B Col C Col D Col E Col F Products Product 1 Product 2 Product 3 Product 4 Rate $5.00 $2.00 $3.00 $4.00 Total 5 2 =formula =SUMPRODUCT(B3:E3,$B$2:$E$2) If this post helps click Yes --------------- Jacob Skaria "JFro" wrote: I have a speadsheet containing customer order details. If a customer orders 10 of one item and 5 of another item, I enter 10 in the column of the first item, and 5 in the column of the second item...etc I would like to be able to work out the total cost of the items in another column automatically. Normally I would do £4.50 x 10 if a customer wanted 10 of an item, and so on manually until I had their grand total. Is there anyway I can do this automatically? Something like: SUM (IF cell A1 contains any value multiply by £4.50 (IF B1 contains any value multiply by £7.50...)) and so on across the speadsheet. Could I also put the item costs elsewhere in the speadsheet to make it easier it update price changes? Many thanks for any suggestions/help :) Jess |
If cell contains a value, multiply...
Thanks for the reply. This would be fine...but on thinking about it more,
there is another complication - the unit price is different if the customer orders more plants! I'm thinking maybe the calculator is easier after all... "Jacob Skaria" wrote: Try the formula with your data placed in the below manner Row 1 contains the product names Row 2 the corresponding rates Row 3; try entering the quantities Col A Col B Col C Col D Col E Col F Products Product 1 Product 2 Product 3 Product 4 Rate $5.00 $2.00 $3.00 $4.00 Total 5 2 =formula =SUMPRODUCT(B3:E3,$B$2:$E$2) If this post helps click Yes --------------- Jacob Skaria "JFro" wrote: I have a speadsheet containing customer order details. If a customer orders 10 of one item and 5 of another item, I enter 10 in the column of the first item, and 5 in the column of the second item...etc I would like to be able to work out the total cost of the items in another column automatically. Normally I would do £4.50 x 10 if a customer wanted 10 of an item, and so on manually until I had their grand total. Is there anyway I can do this automatically? Something like: SUM (IF cell A1 contains any value multiply by £4.50 (IF B1 contains any value multiply by £7.50...)) and so on across the speadsheet. Could I also put the item costs elsewhere in the speadsheet to make it easier it update price changes? Many thanks for any suggestions/help :) Jess |
If cell contains a value, multiply...
I had a break and came back to it, and this works I think (where prices are
held in A13 and A14): =SUM(IF(M2=10,A13, IF(M2=25,A14)), IF(O2=10,A13, IF(O2=25,A14))...) Thanks for the help Jacob :) "JFro" wrote: Thanks for the reply. This would be fine...but on thinking about it more, there is another complication - the unit price is different if the customer orders more plants! I'm thinking maybe the calculator is easier after all... "Jacob Skaria" wrote: Try the formula with your data placed in the below manner Row 1 contains the product names Row 2 the corresponding rates Row 3; try entering the quantities Col A Col B Col C Col D Col E Col F Products Product 1 Product 2 Product 3 Product 4 Rate $5.00 $2.00 $3.00 $4.00 Total 5 2 =formula =SUMPRODUCT(B3:E3,$B$2:$E$2) If this post helps click Yes --------------- Jacob Skaria "JFro" wrote: I have a speadsheet containing customer order details. If a customer orders 10 of one item and 5 of another item, I enter 10 in the column of the first item, and 5 in the column of the second item...etc I would like to be able to work out the total cost of the items in another column automatically. Normally I would do £4.50 x 10 if a customer wanted 10 of an item, and so on manually until I had their grand total. Is there anyway I can do this automatically? Something like: SUM (IF cell A1 contains any value multiply by £4.50 (IF B1 contains any value multiply by £7.50...)) and so on across the speadsheet. Could I also put the item costs elsewhere in the speadsheet to make it easier it update price changes? Many thanks for any suggestions/help :) Jess |
All times are GMT +1. The time now is 09:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com