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.

I'm not sure if there's an elegant solution lurking somewhere, but you could
certainly get things going with this simple formulas set-up ..

Presuming 31 empty cols to the right of col AQ
In AR3:
=IF(ISNA(MATCH(H$1,$B$1:$F$1,0)),0,H3/INDEX($B3:$F3,MATCH(H$1,$B$1:$F$1,0)))
Copy AR3 across by 31 cols to BV3, fill down
Then sum it up in AQ3, fill down: =SUM(AR3:BV3)
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Paul66" wrote:
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.