This worked for me as well.

Thanks!

TLazio

"JMB" wrote:

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




"Tlazio" wrote:

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