Need help to calculate sum

[James Reading]

Good Evening

I have made a spreadsheet which is going to be used as our new order form but i'm struggling to finish it off.

I have a section which is called "Hire Duration" and this spreads across B to D.

In each option it has the following options to chose from in a drop down: "6 hours £15" "12 hours £25" "24hours £35".

What i need to do is when a customer selects one of the above the Price/Cost goes into the "Total" section.

The reason it spreads from B to D is because the customer can choose up to 3 different options as per attached.

[#2]

Hi James,

That could be something like

=SUMPRODUCT(RIGHT((B20:D20 & "0"),3)*1)/10

assuming any your price is not more than 100 pounds.

Here we add zero to the end of each string to avoid error in calculation - if any of your options is empty RIGHT() from empty string is also empty and we try to add it to any number result will be error.

SUMPRODUCT() sums all resulting figures, e.g.

="150"+"0"+"350"

gives 500 (will be converted to number automatically), finally divide on 10 to receive 50 pounds.

If any of prices is more than 100 pounds the formula will be more complicated

[#3]

Another option is to use a SWITCH statement:

=SWITCH(B20,"6 hours $15",15,"12 hours $25",25,"24 hours $35",35)+SWITCH(C20,"6 hours $15",15,"12 hours $25",25,"24 hours $35",35)+SWITCH(D20,"6 hours $15",15,"12 hours $25",25,"24 hours $35",35)

It would get more complicated if you had more than 3 options, but it meets the need!

[#4]

Heya,

so just to clarify if i used your formula as it is would that simply get put into the "total cost" field?

From there it should auto calculate ?

Sorry had a long day and my head feels like a slush puppy.