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

I think from what you said is that all of the "7am" indicators are in the A
column and all of the times "smith" is doing the 7am shift is in the B
through N Columns. and your problem is that sometimes there are more than 50
rows with data and sometimes less.

If this is correct, I would just reference a longer section of column A and
B say

=sumproduct(($B1:N100)=7am)*(A1:A100="smith"))

If the problem is that you can go out past column N just change that
reference as long as there would not be a random "7am" outthere for some
other reason.

Sum product in this type of calc will ignore blank cells.

"Graeme Moloney" wrote:

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

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