One way, using a helper column.

1) In an unused column (say "H") enter

H1: =IF(SUBTOTAL(103,A1),A1)

and copy down as far as required, say, H1000. Hide the column.

2) In your target cell, array enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LEN(H1:H1000),-LEN(SUBSTITUTE(H1:H1000,"its","")))/LEN("its")


In article ,
Sedmikraska wrote:

Hello,
I need to count how many times does a word "ITS" appear in my column. the
problem is that it appears multiple times within the same cell but excel
counts that cell just once instead of lets say 5 times when ITS appears 5
times in the cell. and also, my list is filtered so it looks like excel is
including the missing lines as well which i dont need.

thank you