About Us

Tlazio

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
sumproduct vs. countif	Coal Miner	Excel Discussion (Misc queries)	1	June 15th 06 09:34 PM
Countif Formula /Sort Bug???	orcfodder	Excel Discussion (Misc queries)	2	January 12th 06 10:04 AM
COUNTIF or not to COUNTIF on a range in another sheet	Ellie	Excel Worksheet Functions	4	September 15th 05 10:06 PM
Combining IF and COUNTIF based on two columns	maxtrixx	Excel Discussion (Misc queries)	5	March 31st 05 06:21 PM
Countif - Countif	maswinney	Excel Worksheet Functions	3	November 15th 04 11:06 PM

Menu

About Us