HLookup: sum of all cells in between 2 values

Vague, wishy-washy title I know, but I'm not particularly with it today, which is probably why this is alluding me.

I have an annual leave sheet with the days of the week typed across the second row down, underneath which are the hours worked on each particular day. Above each day is the Excel value for each day (Sunday=1, Monday=2 etc). A bit like this:

2         3      4        5     6 
 Mon   Tue   Wed   Thu   Fri
  7.5     7.5     3.5    3.5    7.5  

Date From       Date to           Leave to be taken
09/08/2010      12/08/2010

What I want is for the required annual leave to be calculated in the 'Leave to be taken' column based on the dates entered. E.g. with the example above, I want Monday through Thursday summing and Friday ignoring. Likewise if the last date was 11/08/2010 I'd want Monday through Wednesday summing.

I'm using the Weekday() function with HLookup to match the dates to the day values on the top line, but I don't know how to sum the values between the given dates.

I'm doing it this way because our staff all work weird hours so there isn't a 'one size fits all' spreadsheet. I know there must be way to do this, and with formulae (formulas?) I'm usually pretty good, but this is just to working out.

Any ideas?

So you are working on one week's worth always?

If so, something like

=SUM(INDEX(A3:G3,MATCH(DAY(A6),A1:G1,0)):INDEX(A3:G3,MATCH(DAY(B6),A1:G1,0)))

where A6 and B6 contain start/end dates and A1:G1 contain your day numbers and A3:G3 the values to sum.

Upon reviewing my response, I think I gave you the wrong function... instead of DAY() , you should use WEEKDAY(). The DAY() function is based on day number within the month, whereas WEEKDAY() is based on day number of the week.. which is what I think you were looking for... so please use this instead:

=SUM(INDEX(A3:G3,MATCH(WEEKDAY(A6),A1:G1,0)):INDEX(A3:G3,MATCH(WEEKDAY(B6),A1:G1,0)))

Would it need an entirely new formula, or could the one you gave me be adapted?

It all depends on exactly what you need in the end, but you could adapt the formula if you were to use the actual dates in row 1 instead of day number..

Or it always one month's worth, you could DAY() function as I had previously shown... etc. So really you need to define the problem again to ensure the right formula(s).

I adjusted the formula you gave and used WEEKDAY instead of DAY, but thanks for taking the time to reply with the adjustment.

Essentially, when I say 'multiple weeks' I'm really only talking about 2 - 3 at the absolute most. It's basically an annual leave calculator and using the formula you gave me, it works fine. People just have to book one week at a time. I just know that someone is going to try and book 2 weeks leave in one go (despite having been told otherwise), complain that it is only giving them 1 week's worth of leave and management will decide, in their infinite wisdom, that it should really be capable of calculating more than a single week.

Can you tell I'm not management material?

At the minute the formula looks at the dates entered, works out which day each date is and assigns them a number. It then matches that number with the numbers above the days on the grid and adds together the hours between the two days. Basically what I'm asking is, is there anyway the formula could be adjusted so that should someone enter 2 dates that are more than 1 working week apart (09/08/2010 and 18/08/2010, say), the correct calculations would be performed?

If not, don't worry about it. With the spreadsheet staff are getting, they're getting led by the hands as it is. Thanks for your help, though.

Edit: I should have clarified - the grid with days of the week and hours worked is static and isn't adjusted regularly. It gets filled in at the beginning of the year with peoples working hours, then left alone unless working hours change.