COUNTIF accross multiple worksheets issues

[djarvinen]

I've seen quite a few posts on this problem but haven't been able to
use any of the solutions I've found.

The problem:

(Excel 2003)

=COUNTIF (W1!A1:A5,">1") is fine (W1 = Worksheet Name)

=COUNTIF (W1.W5!A1:A5,">1") broken

(tried with and without quotes around worksheet names)

I want to solve this without using any addons, without resorting to
VBA, and it would be nice (but not possible?) to solve without an
INDIRECT and creating lists/ranges of work sheet names. In other
words, I would like a simple and elegant solution.

As an aside rant, why doesn't this work? COUNTA and other functions
seem to have no problem with worksheet ranges.

 

[Joel]

If you have only 5 sheets then use this

=COUNTIF (W1!A1:A5,">1") +COUNTIF (W2!A1:A5,">1")+COUNTIF
(W3!A1:A5,">1")+COUNTIF (W4!A1:A5,">1")+COUNTIF (W5!A1:A5,">1")

 

[djarvinen]

If you have only 5 sheets then use this

=COUNTIF (W1!A1:A5,">1") +COUNTIF (W2!A1:A5,">1")+COUNTIF
(W3!A1:A5,">1")+COUNTIF (W4!A1:A5,">1")+COUNTIF (W5!A1:A5,">1")

Thanks; that seems like it would work fine.

But I finally 'bit it' and used the INDIRECT funtion. I don't like it
because I have to edit a LOT of fields when I add a new worksheet.

Why can't they just fix COUNTIF?

 

[Joel]

I don't know why they can't fix the problem. It probably effects a lot of
functions. My guess by changing the code it will break other peoples
workbooks that are relying on bugs so they will work.

The main problem is the microsoft code wasn't tested very well in the 1st
place. New enhancements were rushed to market to beat the competion. Lotus
was good, Quatro was GREAAAAAAAAAT, and Excel was PooooooooooooooooR. but
eventually Excel won the rat race.

 

[djarvinen]

I don't know why they can't fix the problem.  It probably effects a lotof
functions.  My guess by changing the code it will break other peoples
workbooks that are relying on bugs so they will work.

The main problem is the microsoft code wasn't tested very well in the 1st
place.  New enhancements were rushed to market to beat the competion.  Lotus
was good, Quatro was GREAAAAAAAAAT, and Excel was PooooooooooooooooR.  but
eventually Excel won the rat race.

I just realized that my list of Worksheet names in my INDIRECT
function is in quotes, thus the field ranges aren't adjusted when I
'copy' the function. That's about 3 seperate edits in 90 cells I have
to do hand.