Formula For Total Charges Based on Dates and Holidays

[Tech56]

Hello,

In cell C3 I would like a formula to calculate the Total charges based on the Check In and Check Out dates. This needs to take into account the rates for the Seasons and if there are any Holidays in the date range which has the higher rate in P7. I put the amount in C3 by adding each day together as you can see in P10:R24 with the result in R25.

Thank you very much

 

[Chihiro]

Personally, I'd not attempt to solve this in single cell.

I'd set up intermediate table to supply results, like you did in your sheet, but with named ranges and formulas.

1. Add named ranges.
CheckIn ='Hotel Rates'!$C$1
CheckOut ='Hotel Rates'!$C$2
Holiday_Rate ='Hotel Rates'!$P$7
Holidays ='Hotel Rates'!$M$7:$M$12
Rates ='Hotel Rates'!$E$7:$K$9
SeasonEnd ='Hotel Rates'!$D$7:$D$9
SeasonStart ='Hotel Rates'!$C$7:$C$9
Wkday ='Hotel Rates'!$E$6:$K$6

2. Add new sheet named "Calc" and add table with headers [Date] and [Rates]

3. For Date column add formula:

=IF(CheckIn+ROWS($A$1:A1)-1<=CheckOut,CheckIn+ROWS($A$1:A1)-1,"")

4. For Rates add formula:

=IF(ISNUMBER(MATCH([@Date],Holidays,0)),Holiday_Rate,IFERROR(INDEX(Rates,SUMPRODUCT((SeasonStart<=[@Date])*(SeasonEnd>=[@Date]),ROW($1:$3)),MATCH(TEXT([@Date],"dddd"),Wkday,0)),""))

5. Extend table range to as many rows as needed (I set up for 99 days).

Then in C3:
=SUM(Table1[Rates])

See attached.

There are other ways to do this, but this will make it easier to follow calculation steps and allows visual check when needed.

 

[Tech56]

Brilliant solution thanks so much!

 

[zrs]

Dear Chihiro, your last post is very helpful for me clarified my mind how to build such a sheet. Thanks also Tech56 for his query.
But calculation goes wrong when I change just a rate at periods, and total changed to Holiday rate of $ 140. The total never recovered even if I retrieve the original rate. I have excel2010 Turkish and not sure whether happened cause of version. Thanks in advance for your kindest prompt.