countif function in multiple worksheets

I have been trying to use the countif function to count the number of times a
"Y" response occurs in a specific cell in 15 different worksheets that are
all in one workbook. ... I have tried
=SUM(COUNTIF('1:15'!B51,{"Y","N"}) but I keep on getting a "value error".
This formula works in one worksheet but it wont count multiple worksheets.
I've been trying to solve this for days by myself but I'm stuck. I'm a new
user and really would appreciate any help.

Thank you

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:15"))&"'!B51"),{"Y","N"}))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ferde" <ferde@discussions.microsoft.com> wrote in message
news:9B077C64-517C-4667-B833-CCC1210486D3@microsoft.com...
> I have been trying to use the countif function to count the number of
times a
> "Y" response occurs in a specific cell in 15 different worksheets that
are
> all in one workbook. ... I have tried
> =SUM(COUNTIF('1:15'!B51,{"Y","N"}) but I keep on getting a "value
error".
> This formula works in one worksheet but it wont count multiple
worksheets.
> I've been trying to solve this for days by myself but I'm stuck. I'm a
new
> user and really would appreciate any help.
>
> Thank you

Bob Thank you Thank you Thank you.... I have been working on this for days
and you solved it for me . I really appreciate your help

"ferde" wrote:

> I have been trying to use the countif function to count the number of times a
> "Y" response occurs in a specific cell in 15 different worksheets that are
> all in one workbook. ... I have tried
> =SUM(COUNTIF('1:15'!B51,{"Y","N"}) but I keep on getting a "value error".
> This formula works in one worksheet but it wont count multiple worksheets.
> I've been trying to solve this for days by myself but I'm stuck. I'm a new
> user and really would appreciate any help.
>
> Thank you

You should have asked earlier <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ferde" <ferde@discussions.microsoft.com> wrote in message
news:94BC721D-3DC0-4894-81A4-29BA32C17DF6@microsoft.com...
> Bob Thank you Thank you Thank you.... I have been working on this for
days
> and you solved it for me . I really appreciate your help
>
> "ferde" wrote:
>
> > I have been trying to use the countif function to count the number of
times a
> > "Y" response occurs in a specific cell in 15 different worksheets that
are
> > all in one workbook. ... I have tried
> > =SUM(COUNTIF('1:15'!B51,{"Y","N"}) but I keep on getting a "value
error".
> > This formula works in one worksheet but it wont count multiple
worksheets.
> > I've been trying to solve this for days by myself but I'm stuck. I'm a
new
> > user and really would appreciate any help.
> >
> > Thank you