Solved: countif contains multiple text values

[waysofolde]

Need to know if there is a formula for counting text in Excel that would count the cell if if contains multiple values.

for example:

I am wanting Excel to count the range of cells in column J that contain the word football. However, some of the cells contain football, baseball.
and other will have baseball, football.

Is there anyway that I can write the formula that would count the cells if the word is showing at all?

Thanks in advance for any and all help.

[unmarkedhelicopter]

Football ??? don't you call that Soccer ?
This is a tricky one which lends itself to a UDF but here's an array that does the job by cheating
=SUM(IF(LEN(J2:J100)<>LEN(SUBSTITUTE(J2:J100,"football","")),1,0))
remember commit this formula with ctrl-shft-enter

[waysofolde]

Thank you very much. That works perfectly

[Bob Phillips]

Another, non-array formula

=SUMPRODUCT(--(ISNUMBER(SEARCH("football",J2:J100))))

[unmarkedhelicopter]

Another non-array formula :-

=countif(J2:J100,"*football*")