Count

[felix]

i have one big table, need help, how do to these things funcions

1. Need to count B5 to B35 names, how meny thimes names recur.
example:
sony
philips
samusng
thomson
sony
nokia
sony
philips

and need result like this
sony 3
philips 2
samsung 1
thomson 1
etc

2. Need from all table nubers and txt find some words
example:
plaaplaa Sony pla plap lpla Sony
111sdas pla pla Sony.

And will find all Sony from this txt and cont me like this
Sony 3

Sry bad english i hope u understand what i need.

 

[felix]

these 1. and 2.
Not need that listing
samusng 1
thomson 1

need if word recur in txt more then 2-3 times then it will count it.

nr2 example more
(One box)
ag234 kama 0taba laha 45
ag284 kama maba laha 48
ag234 kama ghba laha 45
ag534 kama taiul laha 90

so here count 2 > ag234

 

[N harkawat]

1) Using a simple Pivot table you could get the count...Search google for
using pivot table if unsure

2) =SUM(--(MID(a1,ROW(INDIRECT("1:"&LEN(a1))),len(b1))=b1))
where A1 is your text and cell B1 is value you are searching within the Text
"eg"Sony" etc)

After copying the formula press Ctrl+shift+enter

 

[Pete_UK]

I'm not sure if you have a separate list of names in 1. or if you just
want the counts against every name. Assuming your names are in column
A, then put this formula in B1:

=COUNTIF(A:A,B1)

then copy it down.

If you have a separate list of names, say beginning in D1, then put
this formula in E1:

=COUNTIF(A:A,D1)

and copy down.

For 2. you can use:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"Sony","")))/LEN("Sony")

assuming your long text is in A1.

Hope this helps.

Pete