The best way is with a UDF function. Try this

=GetPrice(A1:H3,1,A7,B7)

A1:H3 is the table range including the header row and header column like you
would use in a vlookup.

The 1 indicates the row offset from the header. With two diffferent units
it would be iehter a 1 or a 2.

A7 is the StartDate as a real date in the worksheet like 8/15/09

A8 is the End Date as a real date in the worksheet like 8/25/09

The UDF is the following macro

Function GetPrice(PriceTable As Range, TableOffset As Integer, _
StartDate As Date, EndDate As Date)
GetPrice = 0
For MyDate = StartDate To EndDate
Wday = Format(MyDate, "ddd")
Set c = PriceTable.Rows(1).Find(what:=Wday, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
GetPrice = GetPrice + c.Offset(TableOffset, 0)
End If

Next MyDate

End Function


"nba" wrote:

Joel,

what i am after is the sum of multiple days booking in one unit. in this
example a booking for 5 nights in unit 1 starting wed what is the total? eg:
unit = a2, days = e2+f2+g2+h2+b2=480
The start day changes all the time with each new booking so I am not sure
how to loop arround through the days of week. Your help is appreciated.

"Joel" wrote:

I fwouold use two sumproducts. I'll put rows and column into your data to
show you how

A B C D E F G H
1 sun mon tue wed thu fri sat
2 unit 1 100 90 90 90 90 100 100
3 unit 2 150 100 100 100 100 150 150


find the minum rates from row 2 comparing with row 3

=Sumproduct(--(B2:H2<=B3:H3),B2:H2)

now the same in 2nd row. I'm only using less than

=Sumproduct(--(B3:H3<B2:H2),B3:H3)

Now put the two formulas into one
=Sumproduct(--(B2:H2<=B3:H3),B2:H2) + Sumproduct(--(B3:H3<B2:H2),B3:H3)



"nba" wrote:

I have 2 sheets. One with 365 dates showing low, mid or high season. The
second sheet has 3 tables listing multiple units with different pricing for
each day of the week for each season. EG:

table 2 rates low season
sun mon tue wed thu fri sat
unit 1 100 90 90 90 90 100 100
unit 2 150 100 100 100 100 150 150

I am using
VLOOKUP(H9,INDIRECT(VLOOKUP(F11,Dates,2,FALSE)),WE EKDAY(D11)+2,FALSE) to find
the right rate for the unit (H9) for the right date but now i need to add
each night of the booking for example 5 nights starting wed in unit 1.

that would be 90+90+100+100+100=480

can you help.