Calculating Revenues From Two Separate Tables With Conditional Statements

[HwoodL]

Hello Guys,

Your assistance in helping me solve the below problem would be greatly appreciated.

I would like to calculate parking revenues for clients who use "domestic" or "international" parking facilities.

The value for such parking will be determined also by the type of vehicle parked "car" or "truck".

The rates associated with parking are dependant upon the type of facility used "domestic" or "international". I would like to have the revenue field populated with values, regardless of the filters used (e.g. Domestic, Truck, days in parking 15 days). There is a cumulative scale of parking in use as indicated on pictures below.

I want to compare the values of parking currently against the alternative rates that are listed in the parking rates screen shot (grey and blue picture).

Your excel guru skills are very much needed asap!!

I look forward to hearing from you.

Thanks,

 

[HwoodL]

Hi Guys,

I have just reviewed my initially post and realised I did not make it super clear my apologies. I am unable to upload the mini excel file for your to review!!

I would like to calculate Parking Revenues in Column E in the pic based on the selection of Column B Location ( Domestic or International) and Column C Vehicle (Car or Truck). The selections made in Column B and C, will lead to the correct the parking rate table to be selected with the associate values.

The parking rate table is on a separate tab compared to where the Parking Revenues Column is.

The rate values are cumulative dependent on the number of days parked.

Table explanation
Car example:
- First 3 days cost £ 7 per day
- Day 4-6 cost £ 16 per day
- Day 7-10 cost £ 20 per day
- From day 11 and onwards cost £ 30 per day

I would like Revenues in Column E6 populated with a values based on the criteria of Location = Domestic and Vehicle = Car using the pricing logic above. Below is an example of what I am seeking.

6 days Total Parking
First 3 days : @ £7 per day (3 * £7 = 21)
4 - 6 days : @ £16 per day (3 * £16 = £48).
So the total of £21 + £48 = £69 displayed in Column E6 Revenues.

I have enclosed a revised picture of the parking rates.

Your assistance is much appreciated as I have a heard deadline this weekend!

I hope my revised explanation helps remove any ambiguity.

Thanks