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

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

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

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