Counting items within a cell

[Guest]

Hello

Thanks for helping if you do
I want to count a specific entry within each cell. I tried countif but it counted every cell that contained anything. Below are the contents of 3 cells two of which contain the entry 0009AA. There are 2 cells in this case. How would I write a worksheet function that would only count those cells that have that entry, and it could be located anywhere within the cell as evidenced below

23-2007AA(QTY:1) - (10/01/03-09/30/04), 004103(QTY:1) - (10/01/03-09/30/04
004103(QTY:1) - (10/01/03-09/30/04), 0009AA(QTY:1) - (10/01/03-09/30/04), 23-2007AA(QTY:1) - (10/01/03-09/30/04

0009AA(QTY:1) - (10/01/03-09/30/04), 004103(QTY:1) - (10/01/03-09/30/04), 23-2007AA(QTY:1) - (10/01/03-09/30/04

23-2007AA(QTY:1) - (10/01/03-09/30/04), 004103(QTY:1) - (10/01/03-09/30/04

I am trying to get a count of all entries that contain that string within it and I am doing this in EXCEL 2000. Any help or suggestions would be GREATLY appreciated

Thanks in advanc

Kel

 

[Peo Sjoblom]

One way

=COUNTIF(A1:A3,"*0009AA*")

--

Regards,

Peo Sjoblom

Hello,

Thanks for helping if you do!
I want to count a specific entry within each cell. I tried countif but it

counted every cell that contained anything. Below are the contents of 3
cells two of which contain the entry 0009AA. There are 2 cells in this
case. How would I write a worksheet function that would only count those
cells that have that entry, and it could be located anywhere within the cell
as evidenced below.

23-2007AA(QTY:1) - (10/01/03-09/30/04), 004103(QTY:1) - (10/01/03-09/30/04)
004103(QTY:1) - (10/01/03-09/30/04), 0009AA(QTY:1) - (10/01/03-09/30/04),

23-2007AA(QTY:1) - (10/01/03-09/30/04)

0009AA(QTY:1) - (10/01/03-09/30/04), 004103(QTY:1) - (10/01/03-09/30/04),

23-2007AA(QTY:1) - (10/01/03-09/30/04)

23-2007AA(QTY:1) - (10/01/03-09/30/04), 004103(QTY:1) - (10/01/03-09/30/04)

I am trying to get a count of all entries that contain that string within

it and I am doing this in EXCEL 2000. Any help or suggestions would be
GREATLY appreciated.

 

[Steve]

I'm not sure if this is quite what you are looking for,
but it works in my test and isn't too bad of a workaround.
Insert a column somewhere to evaluate the long strings you
have sampled. In that column, place the following formula:
=FIND("0009AA",A1) (obviously, your reference will be
different) Fill down through your records of information.
Whereever you want the full count, put the following
formula:
=COUNTIF([column reference above goes here],">0")
Hope that helps...

-----Original Message-----
Hello,

Thanks for helping if you do!
I want to count a specific entry within each cell. I

tried countif but it counted every cell that contained
anything. Below are the contents of 3 cells two of which
contain the entry 0009AA. There are 2 cells in this
case. How would I write a worksheet function that would
only count those cells that have that entry, and it could
be located anywhere within the cell as evidenced below.

23-2007AA(QTY:1) - (10/01/03-09/30/04), 004103(QTY:1) - (10/01/03-09/30/04)
004103(QTY:1) - (10/01/03-09/30/04), 0009AA(QTY:1) -

(10/01/03-09/30/04), 23-2007AA(QTY:1) - (10/01/03-09/30/04)

0009AA(QTY:1) - (10/01/03-09/30/04), 004103(QTY:1) -

(10/01/03-09/30/04), 23-2007AA(QTY:1) - (10/01/03-09/30/04)

23-2007AA(QTY:1) - (10/01/03-09/30/04), 004103(QTY:1) - (10/01/03-09/30/04)

I am trying to get a count of all entries that contain

that string within it and I am doing this in EXCEL 2000.
Any help or suggestions would be GREATLY appreciated.

 

[Guest]

Thank you Peo
I didn't realize that you could use wild cards inside the quotes

One more small thing though which I forgot to add to the last message

How can I get it to Highlight the cell where the information resides? Just some way to change it so that it is easy to find the actual data within the spreadsheet. The spreadsheet can have about 15000 entries and we need to find that data

Thanks again ma
Ke

----- Peo Sjoblom wrote: ----

One wa

=COUNTIF(A1:A3,"*0009AA*"

--

Regards

Peo Sjoblo

Hello
I want to count a specific entry within each cell. I tried countif but i

counted every cell that contained anything. Below are the contents of
cells two of which contain the entry 0009AA. There are 2 cells in thi
case. How would I write a worksheet function that would only count thos
cells that have that entry, and it could be located anywhere within the cel
as evidenced below

(10/01/03-09/30/04
004103(QTY:1) - (10/01/03-09/30/04), 0009AA(QTY:1) - (10/01/03-09/30/04)

23-2007AA(QTY:1) - (10/01/03-09/30/04it and I am doing this in EXCEL 2000. Any help or suggestions would b
GREATLY appreciated

 

[Peo Sjoblom]

Ok, that takes a slightly different approach, select the range of data (we
still use my example of data starting in A1)
with A1 as the active cell (if you select a range in excel the active cell
is the one that looks white while the rest is bluish using default), so
start from the cell you are going to reference to, I start select A1 to A3,
the I do Format>Conditional Formatting, formula is and there I put

=ISNUMBER(FIND("0009AA",A1))

then click the format button, select patterns (I usually select either light
blue or light yellow since they are
less disturbing and don't block the text), finish by clicking OK twice..

So, if your data starts in D4 going to H15000 something, click in the name
box above the row headers and type
D14:H15000, press enter. then use

=ISNUMBER(FIND("0009AA",D4))
--

Regards,

Peo Sjoblom

Thank you Peo,
I didn't realize that you could use wild cards inside the quotes.

One more small thing though which I forgot to add to the last message;

How can I get it to Highlight the cell where the information resides?

Just some way to change it so that it is easy to find the actual data
within the spreadsheet. The spreadsheet can have about 15000 entries and we
need to find that data.

 

[Guest]

Thank you to you both You have helped greatl

Kel