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