PDA

View Full Version : Solved: countif contains multiple text values



waysofolde
12-18-2007, 11:41 AM
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
12-18-2007, 12:08 PM
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
12-18-2007, 12:42 PM
Thank you very much. That works perfectly

xld
12-18-2007, 01:32 PM
Another, non-array formula




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

unmarkedhelicopter
12-21-2007, 04:01 PM
Another non-array formula :-

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