Many thanks

It just gets better. I can't use the MOREFUNC.XLL as I work in a strictly
controlled environment where it would not be possible to distribute the
Add-in

However, the SUMPRODUCT option is looking good

Regards

Trevor


"Aladin Akyurek" wrote in message
...
If you have Longre's free morefunc.xll add-in:

=COUNTIF.3D('Person 1':'Person 5'!$C8,"Off")

Otherwise:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!C8") ,"Off"))

where SheetList refers to a range housing the relevant sheetnames in
separate cells.

Trevor Shuttleworth wrote:
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