=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!B1:B10") ,"Y"))

where C1:C3 is a range housing the relevant sheetnames in
separate cells, and B1:B10 is the range being checked.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"merlin" wrote in message
...
I need to add up the number of times there is a "Y" in a cell on a
multi-worksheet questionnaire (one sheet per respondant).

COUNTIF doesn't seem to work across multiple sheets. Is there a way around
this (without resorting to VBA)?

I'm thinking I might have to double up on each sheet, convert the Ys to 1s
and sum them.