Hi, what am I doing wrong with the following formula:

=countif(sheet1:sheet31!E6:E35,"=1")

FOR some reason this formula fails when I use the (sheet1:sheet31) with
the column range (E6:E35).

Is it possible to include a multiple sheet count and column range in
the same Formula? Thanks for any helpful responses..

By the way, the formula works ok just as long as I'm not trying to read
multiple sheets.. Please help on this.


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=558346

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT ("1:31"))&"'!E6:E35"),"=1
"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" wrote in message
...

Hi, what am I doing wrong with the following formula:

=countif(sheet1:sheet31!E6:E35,"=1")

FOR some reason this formula fails when I use the (sheet1:sheet31) with
the column range (E6:E35).

Is it possible to include a multiple sheet count and column range in
the same Formula? Thanks for any helpful responses..

By the way, the formula works ok just as long as I'm not trying to read
multiple sheets.. Please help on this.


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=558346

Thanks Bob for the fast response, Much appreciated..

I keep getting an Invalid Cell Reference Error with this formula,
should I substitute any data in the formula to adapt to my sheet names
as I displayed? My sheets are labled (1ST:31ST) eg. 1ST, 2ND, 3RD
...31ST etc....

thanks in advance..


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=558346

Sorry, I picked up on sheet1:sheet31 as in your post.

As there is no way to deduce the sheet names in this format, you will need
to store them in M1:M31 and use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M31&"'!E6:E35" ),"=1"))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" wrote in message
...

Thanks Bob for the fast response, Much appreciated..

I keep getting an Invalid Cell Reference Error with this formula,
should I substitute any data in the formula to adapt to my sheet names
as I displayed? My sheets are labled (1ST:31ST) eg. 1ST, 2ND, 3RD
..31ST etc....

thanks in advance..


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=558346

Following on, if you changed the names to Day1, Day2, etc. you cou;ld then
use

=SUMPRODUCT(COUNTIF(INDIRECT("'Day"&ROW(INDIRECT(" 1:31"))&"'!E6:E35"),"=1")
)


without defining the names

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" wrote in message
...

Thanks Bob for the fast response, Much appreciated..

I keep getting an Invalid Cell Reference Error with this formula,
should I substitute any data in the formula to adapt to my sheet names
as I displayed? My sheets are labled (1ST:31ST) eg. 1ST, 2ND, 3RD
..31ST etc....

thanks in advance..


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=558346

Now I see, Thanks again Bob...

Don't want to push my questioning here, but using that same formula how
can I modify it to check and count for duplicate values? If
Possible...

That long formula on my first question in this thread is doing just
that, "Finding Duplicates" and counting when they are found. I just
didn't want such a long formula to accomplish a small task for each
row.. Thanks


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=558346