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

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

"Peo Sjoblom" wrote:

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


"Steve" wrote:

Peo,

I am somewhat of a novice so pardon the lameness of these questions...

1) How do I put all of the sheet names in a range and rename it?
2) In your work around:
=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2}&"'!"&C ELL("address",A1)),"0"))

In your range for worksheets, "sheet"&{1,2}&CELL("address",A1)...... can I
substitute the names I have given these worksheets for something here? And,
what does "address" refer to.....

Thanks for all your help.... my mind is bigger than my excel skills...

Steve



"Peo Sjoblom" wrote:

Here is a workaround

http://tinyurl.com/97psj



Regards,

Peo Sjoblom

"Steve" wrote:

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

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


"Steve" wrote:

Peo,

I am somewhat of a novice so pardon the lameness of these questions...

1) How do I put all of the sheet names in a range and rename it?
2) In your work around:
=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2}&"'!"&C ELL("address",A1)),"0"))

In your range for worksheets, "sheet"&{1,2}&CELL("address",A1)...... can I
substitute the names I have given these worksheets for something here? And,
what does "address" refer to.....

Thanks for all your help.... my mind is bigger than my excel skills...

Steve



"Peo Sjoblom" wrote:

Here is a workaround

http://tinyurl.com/97psj



Regards,

Peo Sjoblom

"Steve" wrote:

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

Here is a workaround

http://tinyurl.com/97psj



Regards,

Peo Sjoblom

"Steve" wrote:

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

Try

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$Z$1:$Z$2&"'!"&CE LL("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

"Steve" wrote:

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

"Peo Sjoblom" wrote:

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


"Steve" wrote:

Peo,

I am somewhat of a novice so pardon the lameness of these questions...

1) How do I put all of the sheet names in a range and rename it?
2) In your work around:
=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2}&"'!"&C ELL("address",A1)),"0"))

In your range for worksheets, "sheet"&{1,2}&CELL("address",A1)...... can I
substitute the names I have given these worksheets for something here? And,
what does "address" refer to.....

Thanks for all your help.... my mind is bigger than my excel skills...

Steve



"Peo Sjoblom" wrote:

Here is a workaround

http://tinyurl.com/97psj



Regards,

Peo Sjoblom

"Steve" wrote:

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

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

"Peo Sjoblom" wrote:

Try

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$Z$1:$Z$2&"'!"&CE LL("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

"Steve" wrote:

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

"Peo Sjoblom" wrote:

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


"Steve" wrote:

Peo,

I am somewhat of a novice so pardon the lameness of these questions...

1) How do I put all of the sheet names in a range and rename it?
2) In your work around:
=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2}&"'!"&C ELL("address",A1)),"0"))

In your range for worksheets, "sheet"&{1,2}&CELL("address",A1)...... can I
substitute the names I have given these worksheets for something here? And,
what does "address" refer to.....

Thanks for all your help.... my mind is bigger than my excel skills...

Steve



"Peo Sjoblom" wrote:

Here is a workaround

http://tinyurl.com/97psj



Regards,

Peo Sjoblom

"Steve" wrote:

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

On Tue, 31 May 2005 16:10:23 -0700, "Steve"
wrote:

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