Thanks Peo ... and also JE and Aladin

Three answers within 30 minutes ... truly impressive. I've opted for the
SUMPRODUCT Option with the list of worksheet names on a Control Sheet.

I've actually defined a dynamic named range which allows me to home in on a
subset of the worksheets ... and make the formula more informative (I think)

=SUMPRODUCT(COUNTIF(INDIRECT("'"&PeopleList&"'!"&C ELL("address",C8)),"Off"))

This version also has the advantage that I can drag the formula down and the
cell will automatically change ... C8 to C9 to C10, etc

The named range PeopleList is defined as:
=OFFSET(Controls!$H$4,,,COUNTA(Controls!$I:$I),)

I list the sheet names in column H on the Controls sheet and then put real
names next to them in Column H

Thanks again for all the help

Trevor


"Peo Sjoblom" wrote in message
...
Assuming that the sheets are not called person1 but maybe a persons name
you need to put all sheet names in question in a range like in

person1
person2
person3
etc..

now assume you have 5 different sheet names and put them in H1:H5

then you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H5&"'!C8"),"OF F"))


if the sheets have the same name plus a number you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'Person"&ROW(INDIREC T("1:5"))&"'!C8"),"OFF"))

--
Regards,

Peo Sjoblom

(No private emails please)


"Trevor Shuttleworth" wrote in message
...
How can I turn a formula like:

=COUNTIF('Person 1'!$C8,"Off")+COUNTIF('Person
2'!$C8,"Off")+COUNTIF('Person 3'!$C8,"Off")+COUNTIF('Person
4'!$C8,"Off")+COUNTIF('Person 5'!$C8,"Off")

Into (something like):

=COUNTIF('Person 1':'Person 5'!$C8,"Off") ... this gives #NAME?
=COUNTIF('Person 1:Person 5'!$C8,"Off") ... this gives #VALUE!

It doesn't have to be the COUNTIF function so long as it achieves the
same ends.

I need to be able to add more people without a vast amount of repetition.
It would look neater and simpler too!

Thanks in advance

Trevor