Can Someone tell me why this won't work?????????????

S

skijsh1979

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

Bob Phillips

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"),"GP38"))

obviously adjust to n to suit.

--
HTH

Bob

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

skijsh1979

does any thing work in 3d?

Bob Phillips said:
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"),"GP38"))

obviously adjust to n to suit.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
R

Ron Rosenfeld

does any thing work in 3d?
Click to expand...

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
 
S

skijsh1979

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

Peo Sjoblom

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

Ron Rosenfeld

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.
Click to expand...

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
 
S

skijsh1979

It is not working, the result is REF#.

The formula that I have entered is:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"GP38"))

I have listed the titles of all the sheets that need need to be checked for
the occurance of "GP38 in cell I1 of each respective sheet. I must be doing
something wrong.
 
B

Bob Phillips

send me your workbook, I will look at it for you

note my signature re my email address

--
HTH

Bob

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

skijsh1979

Bob,
You should have 2 copies, the first can be trashed. The second has an
explanation of what I am trying to do at this point. I think I did alot of
it the hard way, so if there is anything you see that can be done more
efficiently, let me know.

Thanks

Justin
 
B

Bob Phillips

Nothing has arrived here.

--
HTH

Bob

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

skijsh1979

I sent it again. I somewhat past the previous problem. I am now trying to
count text strings based on more than one criteria.

Example:

A B C D E F G
1 a b c a f d a
2 z y v z y v z
3 y z y x z y y
4 z z z v y y x
5 x v x z v z y
6 z y y
7 y z v

I want to count the number of times "y" shows up a a column that has the
string "a" in row 1 of the particular column or columns. Is there a way to
count just that data?

Justin
 
B

Bob Phillips

=SUMPRODUCT((A2:G7="y")*(A1:G1="a"))

--
HTH

Bob

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

skijsh1979

Bob,

is there a way to identify the different text strings in a certain area and
then list them all on a seperate sheet?

Justin
 
B

Bob Phillips

No, I never did.

--
HTH

Bob

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

Bob Phillips

Which strings, the SP gets told what they are?

--
HTH

Bob

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

skijsh1979

I have M2:AS32 that have a lists of locomotive defects. I want to create
another list that has only 1 of each of the defects that are present in that
group of cells. I don't know how many there will be.
 
S

skijsh1979

The dataset is the same as the example I made. Only catch is that each
defect type can only be listed 1 time in the new list. i am going to be
building frequency ratios based on this data.
 
Top