I have a holiday sheet and am trying to sum the total days taken each month
in columns H to AL based on the contracted hours in columns B to F

CONTRACTED HOURS DAYS OF MONTH
A B C D E F G H I J K L
M ~ AL AQ

1 WEEKDAY 2 3 4 5 6 5 6 7 1 2 3 ~ 4
2 STAFF Mo Tu We Th Fr Total 1 2 3 4 5 6 ~ 31
TOTAL
3 John 9 9 8 7 7 40 7 7 9
3.285
4 Sam 8 8 8 8 8 40 8 8 8 4
3.5

In row 1 above the days of the week for the contracted hours and the days of
the month I have the weekday number (Su=1 to Sa=7).This is used to compare
the day taken against the contract.

In the example above Sam is calculated correctly as he works the same hours
each day buy John works different hours per day so instead of the total being
3 it is 3.285714

I am using the following formula entered as an Array in Cell AQ3 and the
dragged down.

=SUM(H3:AL3/HLOOKUP($H$1:$AL$1,$B$1:F3,ROW(A3)))

I think this due to a range being entered instead of a constant but Excel
doesnt reject it and it is basing the calculation on the first day of the
month. I have tried variations of INDEX, MATCH & SUMIF formulas but I have
hit a brick wall...any help would be greatly appreciated.