=COUNTIF across Worksheets

[Trevor Shuttleworth]

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 1erson 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

 

[JE McGimpsey]

You can change the SUMIF()'s in the solution provided here:

http://www.mcgimpsey.com/excel/threedsumif.html

to COUNTIF()s, and use SUM() to sum across sheets. If you use wrapper
sheets (e.g., all your new sheets go between the first and last), you
can use

=SUM(first:last!Z1)

to include all intermediate sheets.

 

[Aladin Akyurek]

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]

Many thanks.

I'm favouring the SUMPRODUCT option ... but at least now I have some options
to consider which is a huge plus

Regards

Trevor

 

[Peo Sjoblom]

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"),"OFF"))


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

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

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Trevor Shuttleworth]

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

 

[Trevor Shuttleworth]

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&"'!"&CELL("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