Counting occurences of strings in a coloumn

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
 
F

Frank Kabel

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
 
R

robert_woodie

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

cheers
Robert
 
R

robert_woodie

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

thanks
Robert
 
F

Frank Kabel

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
 
R

robert_woodie

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
 
F

Frank Kabel

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
 
A

Alan Beban

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
 
Top