Counting occurences of strings in a coloumn

  • Thread starter Thread starter robert_woodie
  • Start date Start date
R

robert_woodie

i have coloumns of text (one word), these words are repeated and i want
to count the occurences of one word....im sure its possible but cant
find out how to do it...hope some one can help

Cheers
Robert
 
Hi Robert
If you want to count the occurence of one specific word within a range
of columns/row use the following:
=COUNTIF(A1:D100,"testword")
this will count all occurences of 'testword' within A1:D100

HTH
Frank
 
I have done this using the countif function, but want it to count only
filtered data, like the subtotal function does.

cheers
Robert
 
sorry for the confusion frank, i forgot to mention that i only wanted it
to count filtered data.

thanks
Robert
 
Hi Robert
maybe you can explain this a little bit more. Your words are stores in
onyl one column or in >1 columns. Maybe you can post some example data
(in plain text) and your exspected result

Frank
 
NAME TEXT
name1 text1
name1 text1
name1 text2
name2 text1

If i filter this data by name 1. I want to count the occurences o
text1(ie.2) and occurences of text2(ie.1).

Hope this helps!

Robert
 
Hi Robert
I would use a Pivot table for this:
1. Select your range and start the Pivot-table wizard
2. In the layout for the pivot table choose NAME and TEXT as rows and
COUNOF TEXT as data.

This will return a report like the following
NAME TEXT RESULT
name1 text1 2
text2 1
name1 result 3
name2........

for further information on pivot tables have a look at:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm

I hope this is what you need. Of course you can calculate manually each
combination of NAME and TEXT using SUMPRODUCT. e.g.
=SUMPRODUCT((A2:A9999="name1")*(B2:B9999="text1"))
but I think the pivot table is better is this case

HTH
Frank
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook,
with data in A2:B5, text1 in G1, text2 in G2

=ArrayCountIf(ArrayRowFilter1(A1:B5,1,"name1"),G1)

array entered into a two-row column. Adjust range and the two-row column
to suit your data.

Alan Beban
 
Back
Top