Count number of words refer to the list in a cell

Try

B9

Formula:

=SUMPRODUCT(COUNTIF(B$2:B$6,INDEX(TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A9,"1","1 "),"2","2 "),"3","3 "),"4","4 "),"5","5 "))," ",REPT(" ",99)),(ROW(INDIRECT("1:"&99-LEN(SUBSTITUTE($A9," ",""))+1))-1)*99+1,99)),)))

Copy across!

Originally Posted by shukla.ankur281190

Try

B9

Formula:

=SUMPRODUCT(COUNTIF(B$2:B$6,INDEX(TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A9,"1","1 "),"2","2 "),"3","3 "),"4","4 "),"5","5 "))," ",REPT(" ",99)),(ROW(INDIRECT("1:"&99-LEN(SUBSTITUTE($A9," ",""))+1))-1)*99+1,99)),)))

Copy across!

Ankur, you are complicating the formula, Phuocam is good one, if you see the example of op "abc1abc2eee4eee4", here there is no space between words even though he wants the counts. So there is no need of substitute