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.