COUNTIF across all sheets of a workbook

[Guest]

On my summary page of a multiple worksheet work book I want to count tihe
number of "Y" responses in the same cell across the entire workbook. I have
tried countif(worksheet1:worksheetend!C6,"Y") and it doesn't work.... any
help out there?

Thanks,

Steve

 

[Guest]

Peo,

I do plan on copying this formula from cell to cell because I have about 80
cells I have to do the same thing to....

 

[Guest]

The cell address part does not need to be there unless you want to be able to
copy down the formula and have the cell reference change as in A1 would
change to A2
The best way IMHO would be to put all sheet names unless you are using

name number where the name would stay the same but the number would change
like in Excel defaul sheetnames Sheet1, Sheet2 and so on, If we assume you
have unique sheet names, let's assume you have 10 uniques sheet names,
somewhere on the summary sheet (could be off view somewher like Z1:Z10) put
all sheet names.Then assume you want to count cell A1 for a "Y"
That formula would look like


=SUMPRODUCT(COUNTIF(INDIRECT("'"&$Z$1:$Z$10&"'!A1"),"Y"))

Regards,

Peo Sjoblom

 

[Guest]

Here is a workaround

http://tinyurl.com/97psj



Regards,

Peo Sjoblom

 

[Guest]

Try

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$Z$1:$Z$2&"'!"&CELL("address",A1)),"Y"))

replace $Z$1:$Z$10 with the range where yopu put your sheet names

now you can copy down or across and A1 will change accordingly


Regards,

Peo Sjoblom

 

[Guest]

Thanks for you patience, Peo..... I will give it a try....

 

[Ron Rosenfeld]

On my summary page of a multiple worksheet work book I want to count tihe
number of "Y" responses in the same cell across the entire workbook. I have
tried countif(worksheet1:worksheetend!C6,"Y") and it doesn't work.... any
help out there?

Thanks,

Steve

Download and install the free morefunc.xls add-in by Laurent Longre from
http://xcell05.free.fr/ and use the COUNTIF.3D function.


--ron