I am developing a staff roster and wish to know the number of hours
attributed to an indivdual for a week/month

Perhaps you might want to try something along these lines ..

Supposing you have a set-up in Sheet1 as per sample below, dates in col A,
names of staff on duty for the operating hours in cols B to F, all data from
row2 down:

_______ 8:00 9:00 10:00 11:00 12:00
01-Jun-05 Name1 Name1 Name1 Name2 Name2
02-Jun-05 Name2 Name2 Name1 Name1 Name1
03-Jun-05
04-Jun-05
etc

Then in Sheet2, if you have this set-up in A1:D2

StartDate EndDate Name1 Name2
01-Jun-05 02-Jun-05 ? ?

you could put in C2:

=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$A$2 :$A$100<=$B2)*(Sheet1!$B$2
:$F$100=C$1))

and copy C2 across to D2

For the sample data in Sheet1, C2 and D2 will return 6 and 4, which would be
the number of hours that staff: Name1 and Name2 were on duty respectively
between the start and end dates entered in A2:B2, i.e. between 01-Jun-05 to
02-Jun-05 (inclusive)

So you could define the desired period (start / end dates in A2:B2), and
also extend the formula in C2 across to compute accordingly for all the
other staff names which you can list in C1 across.

Adapt the ranges in the formula to suit, but note that you can't use entire
column references (e.g.: A:A, B:B, etc) in SUMPRODUCT

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"easdun" wrote in message
...
I am developing a staff roster and wish to know the number of hours
attributed to an indivdual for a week/month