using countif

[Tlazio]

I am trying to select the same cell (B7) in several excel sheets that are
located in the same workbook, this cell may or may not contain an X, those
that do I want the total amount in a separate sheet. Steps used are:
=countif( select the fisrt sheet, hold the shift key and selceted the last
sheet, select the cell and then complete the formla, hit enter. The formula
is: =countif('Survey 1:Survey 13'!B7,"X") the result result is #Value! The
value should have been 12, what am I doing wrong?

TLazio

 

[Gary''s Student]

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Survey 1","Survey 2","Survey 3","Survey
4","Survey 5"}&"'!B7"),"X"))


or something similar extended for more sheets??

 

[JMB]

From the help file:

You can use 3-D references to refer to cells on other sheets, to define
names, and to create formulas by using the following functions: SUM, AVERAGE,
AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA,
STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA.

But you may be able to do it w/ an array formula. This seems to work okay
on my machine:
=SUM(COUNTIF(INDIRECT("'Survey "&Row(Indirect("1:13"))&"'!B7"),"X"))

entered with Control+Shift+Enter.



Another possible way - if the cell will either be "X" or blank, perhaps you
could use a 1 or 0, then use the SUM function in a 3D reference.

=SUM('Survey 1:Survey 13'!B7))

If you want an "X" to be displayed when the cell value is a 1 and blank for
a 0, you could try a custom format of
[=1]"X";;

and I might use Data Validation to restrict the input to values between 0
and 1 (if the cell is actually an input cell and not the result of a formula).

 

[T. Valko]

Try this:

=SUMPRODUCT(COUNTIF(INDIRECT("'Survey "&ROW(INDIRECT("1:13"))&"'!B7"),"x"))

Biff

 

[Tlazio]

Thanks, I was able to get the results I was looking for.

TLazio

 

[Tlazio]

This worked for me as well.

Thanks!

TLazio

Not sure if my post made it - so I'll try one more time (amended slightly to
use Sumproduct instead of Sum in an array formula after seeing Gary's Student
formula):

From the help file:

You can use 3-D references to refer to cells on other sheets, to define
names, and to create formulas by using the following functions: SUM, AVERAGE,
AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA,
STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA.

This seems to work okay on my machine:
=SUMPRODUCT(COUNTIF(INDIRECT("'Survey "&Row(Indirect("1:13"))&"'!B7"),"X"))

Another possible way - if the cell will either be "X" or blank, perhaps you
could use a 1 or 0, then use the SUM function in a 3D reference.

=SUM('Survey 1:Survey 13'!B7))

If you want an "X" to be displayed when the cell value is a 1 and blank for
a 0, you could try a custom format of
[=1]"X";;

and I might use Data Validation to restrict the input to values between 0
and 1 (if the cell is actually an input cell and not the result of a formula).

I am trying to select the same cell (B7) in several excel sheets that are
located in the same workbook, this cell may or may not contain an X, those
that do I want the total amount in a separate sheet. Steps used are:
=countif( select the fisrt sheet, hold the shift key and selceted the last
sheet, select the cell and then complete the formla, hit enter. The formula
is: =countif('Survey 1:Survey 13'!B7,"X") the result result is #Value! The
value should have been 12, what am I doing wrong?

TLazio