genius.

Is it the ! that stops the incrementation?

Can I use COLUMN in the same way to getthe columns to increment i.e.:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!COLU MN(D1)"&ROW(A11)),"Y"))

"Bob Phillips" wrote in message
...
If you are just going down rows, you could try

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D"&R OW(A11)),"Y"))

which will increment

--
HTH

Bob Phillips

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

"merlin" wrote in message
...
OK - I've changed it to

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$L$2:$L$5&"'!D11" ),"Y"))

so I can just summarise 1 cell at a time. Works beautifully - thanks Bob!

It's a shame that the cell references don't update when I cut and paste
the
formulas - I'll have to change each manually but it's a small price to
pay...


"Bob Phillips" wrote in message
...
=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.