Help Please

M

mikeeee

I need a formula that I can put in a cell to count the number of times
certain word appears in a column of cells. If column A1 to say A50 ha
the word "pass" in 10 different cells then that is the sum I need t
know. Even if the other cells are blank or contains a different word.

Thanks for any Help. It is always greatly appreciated
 
P

Paul Corrado

If "pass" is not the only text in the cell then try

=SUMPRODUCT((ISERROR(FIND("pass",A1:A8))=FALSE)*(A1:A8<>""))

Adjust range as needed
 
F

Frank Kabel

Hi Paul
could be shortened :)))
=SUMPRODUCT(--ISNUMBER(FIND("pass",A1:A8)))

or even
=COUNTIF(A1:A8,"*pass*")
 
P

Paul Corrado

Thanks Frank. I didn't realize you could use the double negative to get the
SUMPRODUCT to work without the last range reference.

Also, it should be pointed out that COUNT or one of its variations will only
work if the text in the target range only includes the one word. Trailing
spaces could be a problem.
 
F

Frank Kabel

Hi
using COUNTIF together with * at the beginning and the end searches for
a cell CONTAINING this word :)
 
Top