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) wit
the column range (E6:E35).

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

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

 

[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]

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)

 

[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]

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