You might try CONCATENATING the Name and Shift columns into a helper column
to read something like Smith,7am or Smith,3pm etc etc..........then use the
same helper column on each sheet and do a normal COUNTIF on that column for
each condition you wish to check, then sum the sums of each sheet into a
master sheet.

Vaya con Dios,
Chuck, CABGx3



"Graeme Moloney" wrote in message
...
I am preparing a roster of staff shifts. I have a new worksheet for each
fortnight. I want to count the number of different shift each person does
for the year.

i.e. count the number of shifts for each staff member starting at 7am, 3pm
11pm or any other start time. The staff member may appear on different
rows
in each worksheet due to staff coming and going out of the work centre. I
have used the sumproduct formula to count the different shifts and if the
staff member appears on different rows. eg
=sumproduct(($B1:N50)=7am)*(A1:A50="smith")) Smith being the staff member
counting the number of 7am shift he does.

This formula appears at the end of the "smith" row in each worksheet to
count the number of 7am shifts worked for that fortnight.

The problem I'm having is the worksheet which records the total 7am shifts
worked etc for the individual staff member over all worksheets as the
cell
reference

changes sometimes in some worksheets due to extra staff being added or
deleted from the worksheet. How can I count the number in these celIs in
all worksheets. I hope I have explained this well enough.



Any assistance appreciated.

Graeme