=COUNTIF across Worksheets

  • Thread starter Trevor Shuttleworth
  • Start date
T

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 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
 
J

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.
 
A

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.
 
T

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
 
P

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)
 
T

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
 
T

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
 
Top