Why Does This Formula Return an Error??

[Mhz]

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.

This is the formula I had to write for each 30 rows X 31 days because I cant get the (sheet:sheet) to work:

=COUNTIF('4TH'!E6:E35,E6)+COUNTIF('2ND'!E6:E35,E6)+COUNTIF('3RD'!E6:E35,E6)+COUNTIF('5TH'!E6:E35,E6)+COUNTIF('6TH'!E6:E35,E6)+COUNTIF('7TH'!E6:E35,E6)+COUNTIF('8TH'!E6:E35,E6)+COUNTIF('9TH'!E6:E35,E6)+COUNTIF('10TH'!E6:E35,E6)+COUNTIF('11TH'!E6:E35,E6)+COUNTIF('12TH'!E6:E35,E6)+COUNTIF('13TH'!E6:E35,E6)+COUNTIF('14TH'!E6:E35,E6)+COUNTIF('15TH'!E6:E35,E6)+COUNTIF('16TH'!E6:E35,E6)+COUNTIF('17TH'!E6:E35,E6)+COUNTIF('18TH'!E6:E35,E6)+COUNTIF('19TH'!E6:E35,E6)+COUNTIF('20TH'!E6:E35,E6)+COUNTIF('21ST'!E6:E35,E6)+COUNTIF('22ND'!E6:E35,E6)+COUNTIF('23RD'!E6:E35,E6)+COUNTIF('24TH'!E6:E35,E6)+COUNTIF('25TH'!E6:E35,E6)+COUNTIF('26TH'!E6:E35,E6)+COUNTIF('27TH'!E6:E35,E6)+COUNTIF('28TH'!E6:E35,E6)+COUNTIF('29TH'!E6:E35,E6)+COUNTIF('30TH'!E6:E35,E6)+COUNTIF('31ST'!E6:E35,E6)

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Mhz]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Mhz]

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