Calc total cost based on room rates

Hi all,

I have just started working at a backpacker hostel and need some help updating an excel sheet that they use. I need quite a complicated IF formula, I believe.

It is for their payment spreadsheet and the necessary columns are as below;

E F G H
Room No. Nights No. Persons Total


There are different prices for the different rooms as follows
Room Numbers 2, 3, 4, 6, 8 and 10 are $39
room 1 is $16
room 5 is $15
room 7 is $12

what we need in column H is a formula that will take the room number, figure out which price it is, multiply it by number of nights, also multiplied by number of people, and finally multiply the total price by 45 to display it in local currency...

the other factor is that rooms 2,3,4,6,8 and 10 are $39 regardless of how many people are in it, whereas rooms 1, 5 and 7 are priced per person, so THOSE rooms need to multiply by number of persons in column G.

So as an example;


E F G H
Room No. Nights No. Persons Total
1 2 1 $1,440
5 14 1 $9,450
2 7 2 $12,285
7 6 2 $6480


I know it is a really complicated one, but if it is possible to create a formula for it that would help out a lot otherwise we have to keep manually calculating the price each time.

I hope someone can help!

Thank you!