To All: I tried both formulas trying to count how many times word " xxxxx" is
mentioned in the sentences that are from column d5 to d300. all that I am
getting is zero.
example: today we will be having xxxxx.
today we will be having yyyyy.
today we will be having zzzzz.
today we will be having xxxxx.

or is there another formula that I should be using ?

"Dave O" wrote:

You can count occurences of XX in col A, for instance, with this
formula:
=COUNTIF(A1:A10,"XX")

Count occurences of AA in col A with BB in col B with this:
=SUMPRODUCT(--(A1:A10="AA"),--(B1:B10="BB"))

COUNTA() counts non-blank cells, and a cell with a space in it is
non-blank, to Excel. If you are sure that the cells contain only one
space, you could use this formula:
=COUNTA(A1:A10)-COUNTIF(A1:A10," ")
That counts your non-blanks and nets out the cells with one space.