=COUNTIF('BNSF 2176:BNSF 8037'!I1,"GP38")

I am attempting to count the number of times the text string "GP38" is
present in cell "I1" of a select group of worksheets within the same work
book. The error I get is "#value" When I run error checking on the funtion,
it says that a value in the formula is of the wrong data type. I was under
the impression that "COUNTIF" could be used for text also.

Thanks,

Justin

It does, but not 3d.

Try this. Put the names of all the target sheets in M1:Mn and use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:Mn&"'!I1"),"GP 38"))

obviously adjust to n to suit.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"skijsh1979" wrote in message
...
=COUNTIF('BNSF 2176:BNSF 8037'!I1,"GP38")

I am attempting to count the number of times the text string "GP38" is
present in cell "I1" of a select group of worksheets within the same work
book. The error I get is "#value" When I run error checking on the
funtion,
it says that a value in the formula is of the wrong data type. I was
under
the impression that "COUNTIF" could be used for text also.

Thanks,

Justin

does any thing work in 3d?

"Bob Phillips" wrote:

It does, but not 3d.

Try this. Put the names of all the target sheets in M1:Mn and use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:Mn&"'!I1"),"GP 38"))

obviously adjust to n to suit.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"skijsh1979" wrote in message
...
=COUNTIF('BNSF 2176:BNSF 8037'!I1,"GP38")

I am attempting to count the number of times the text string "GP38" is
present in cell "I1" of a select group of worksheets within the same work
book. The error I get is "#value" When I run error checking on the
funtion,
it says that a value in the formula is of the wrong data type. I was
under
the impression that "COUNTIF" could be used for text also.

Thanks,

Justin

On Tue, 19 Jun 2007 04:07:03 -0700, skijsh1979
wrote:

does any thing work in 3d?


Usually the quickest way to answer these kinds of questions is to look at HELP
for your particular product.

For example, in Excel 2003:


Functions that can be used in a 3-D reference

SUM - adds numbers
AVERAGE - calculates average (arithmetic mean) of numbers
AVERAGEA - calculates average (arithmetic mean) of numbers; includes text and
logicals

COUNT - counts cells that contain numbers
COUNTA - counts cells that are not empty
MAX - finds largest value in a set of values
MAXA - finds largest value in a set of values; includes text and logicals
MIN - finds smallest value in a set of values
MINA - finds smallest value in a set of values; includes text and logicals
PRODUCT - multiplies numbers
STDEV - calculates standard deviation based on a sample
STDEVA - calculates standard deviation based on a sample; includes text and
logicals

STDEVP - calculates standard deviation of an entire population
STDEVPA - calculates standard deviation of an entire population; includes text
and logicals

VAR - estimates variance based on a sample
VARA - estimates variance based on a sample; includes text and logicals
VARP - calculates variance for an entire population
VARPA - calculates variance for an entire population; includes text and
logicals


--ron

From that list, I don't see anything that will count text strings in 3-d. So
I am guessing that I will have to make a cell on each page that corrosponds
with the particular text string that I am attempting to count? Is there any
other way to accomplish this. I have around 40 sheets and 12 seperate text
strings that I am going to be counting.

"Ron Rosenfeld" wrote:

On Tue, 19 Jun 2007 04:07:03 -0700, skijsh1979
wrote:

does any thing work in 3d?


Usually the quickest way to answer these kinds of questions is to look at HELP
for your particular product.

For example, in Excel 2003:


Functions that can be used in a 3-D reference

SUM - adds numbers
AVERAGE - calculates average (arithmetic mean) of numbers
AVERAGEA - calculates average (arithmetic mean) of numbers; includes text and
logicals

COUNT - counts cells that contain numbers
COUNTA - counts cells that are not empty
MAX - finds largest value in a set of values
MAXA - finds largest value in a set of values; includes text and logicals
MIN - finds smallest value in a set of values
MINA - finds smallest value in a set of values; includes text and logicals
PRODUCT - multiplies numbers
STDEV - calculates standard deviation based on a sample
STDEVA - calculates standard deviation based on a sample; includes text and
logicals

STDEVP - calculates standard deviation of an entire population
STDEVPA - calculates standard deviation of an entire population; includes text
and logicals

VAR - estimates variance based on a sample
VARA - estimates variance based on a sample; includes text and logicals
VARP - calculates variance for an entire population
VARPA - calculates variance for an entire population; includes text and
logicals


--ron

You already got a solution by Bob Phillips, that is the way you do a the
equivalent of a countif over multiple sheets


--
Regards,

Peo Sjoblom



"skijsh1979" wrote in message
...
From that list, I don't see anything that will count text strings in 3-d.
So
I am guessing that I will have to make a cell on each page that
corrosponds
with the particular text string that I am attempting to count? Is there
any
other way to accomplish this. I have around 40 sheets and 12 seperate
text
strings that I am going to be counting.

"Ron Rosenfeld" wrote:

On Tue, 19 Jun 2007 04:07:03 -0700, skijsh1979
wrote:

does any thing work in 3d?


Usually the quickest way to answer these kinds of questions is to look at
HELP
for your particular product.

For example, in Excel 2003:


Functions that can be used in a 3-D reference

SUM - adds numbers
AVERAGE - calculates average (arithmetic mean) of numbers
AVERAGEA - calculates average (arithmetic mean) of numbers; includes text
and
logicals

COUNT - counts cells that contain numbers
COUNTA - counts cells that are not empty
MAX - finds largest value in a set of values
MAXA - finds largest value in a set of values; includes text and logicals
MIN - finds smallest value in a set of values
MINA - finds smallest value in a set of values; includes text and
logicals
PRODUCT - multiplies numbers
STDEV - calculates standard deviation based on a sample
STDEVA - calculates standard deviation based on a sample; includes text
and
logicals

STDEVP - calculates standard deviation of an entire population
STDEVPA - calculates standard deviation of an entire population; includes
text
and logicals

VAR - estimates variance based on a sample
VARA - estimates variance based on a sample; includes text and logicals
VARP - calculates variance for an entire population
VARPA - calculates variance for an entire population; includes text and
logicals


--ron

On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979
wrote:

From that list, I don't see anything that will count text strings in 3-d. So
I am guessing that I will have to make a cell on each page that corrosponds
with the particular text string that I am attempting to count? Is there any
other way to accomplish this. I have around 40 sheets and 12 seperate text
strings that I am going to be counting.

Did you try Bob Phillips suggestion using INDIRECT?

If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll
add-in available at http://xcell05.free.fr/.

This should work if you have XL2003 or earlier, and if the range does not have
more than 65536 cells. I do not know if it will work in XL2007.


--ron