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")

" wrote:

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.