COUNTIF across multiple worksheets
 

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.

COUNTIF across multiple worksheets
 

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

COUNTIF across multiple worksheets
 

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.

COUNTIF across multiple worksheets
 

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.

COUNTIF across multiple worksheets
 

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.

COUNTIF across multiple worksheets
 

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)

"merlin" wrote in message
...
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.

COUNTIF across multiple worksheets
 

Just cracked it

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

--
HTH

Bob Phillips

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

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

"merlin" wrote in message
...
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.

COUNTIF across multiple worksheets
 

magic - thanks, Bob.

"Bob Phillips" wrote in message
...
Just cracked it

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

--
HTH

Bob Phillips

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

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

"merlin" wrote in message
...
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.