COUNTIF across multiple worksheets

[merlin]

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.

 

[Bob Phillips]

=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]

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]

If you are just going down rows, you could try

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

which will increment

--
HTH

Bob Phillips

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

 

[merlin]

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&"'!COLUMN(D1)"&ROW(A11)),"Y"))

 

[Bob Phillips]

No, it is the text within INDIRECT.

Column is harder, and it is late here. I'll work on it tomorrow, check back
then.

--
HTH

Bob Phillips

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

 

[Bob Phillips]

Just cracked it

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

--
HTH

Bob Phillips

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

 

[merlin]

magic - thanks, Bob.

Just cracked it

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

--
HTH

Bob Phillips

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