Counting the total number of cells with specified condition(freque
 

Hi,
Can anyone pls tell me how do i count the total number of cells that has
a certain word appear twice or nth times in a column?

The below-mentioned will be the end result. The frequency will be specified
by the user and excel will count the number of cells that has a certain word
appearing twice in a column. Likewise, for Freqeuncy 3, sums the number of
cells with that has a certain word in a string that appears thrice in the
column.

Frequency(input) Number of occurences(output)
2 10
3 \30

Couldn't quite understand your final output,
but here's something which could help you along the way ..

Assume you have in Sheet1, in A1:A10, the data below

text1 text2
text1 text3
text1 text4
text1 text2
text1 text3
text1 text4
text1 text2
text1 text3
text1 text4
text1 text5

In Sheet2, you have listed in A2:A6

text1
text2
text3
text4
text5

You could put in B2:
=SUMPRODUCT(--ISNUMBER(SEARCH(A2,Sheet1!$A$1:$A$10)))
and copy B2 down to B6

Col B will return the count of the # of cells in Sheet1's A1:A10
for the corresponding text in A2:A6, viz.:

text1 10
text2 3
text3 3
text4 3
text5 1

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kelvin" wrote in message
...
Hi,
Can anyone pls tell me how do i count the total number of cells that
has
a certain word appear twice or nth times in a column?

The below-mentioned will be the end result. The frequency will be
specified
by the user and excel will count the number of cells that has a certain
word
appearing twice in a column. Likewise, for Freqeuncy 3, sums the number
of
cells with that has a certain word in a string that appears thrice in the
column.

Frequency(input) Number of occurences(output)
2 10
3 \30

With your list in column A (from cell A1 to cell A85):
In cell B1:
=SUMPRODUCT(--($A1=$A$1:$A$85))
Copy down to cell A85.
Cells D1 to D10 contain the numbers 1 to 10.
In cell E1:
=SUMPRODUCT(--($B$1:$B$85=$D1))
Copy down to cell E10.

"Kelvin" wrote:

Hi,
Can anyone pls tell me how do i count the total number of cells that has
a certain word appear twice or nth times in a column?

The below-mentioned will be the end result. The frequency will be specified
by the user and excel will count the number of cells that has a certain word
appearing twice in a column. Likewise, for Freqeuncy 3, sums the number of
cells with that has a certain word in a string that appears thrice in the
column.

Frequency(input) Number of occurences(output)
2 10
3 \30