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