Counting list of specific texts inside the cells

Hello Friends

I need to count the list of texts (in Cells D1:D9) in column A, if available then need to plot the count in light yellow cells by formula as shown.

I know I can use the fomula <=COUNTIF(A17,"*"&D1&"*")> For Example in Cell B17 but it leads to combination of 9 different countif and again if my list exceeds from D1:D9 to D1:D100 I cannot add the 100 different countif.

thanks in advance

Try

B2=IF(SUMPRODUCT(--(ISERROR(SEARCH($D$1:$D$9,A2))))=COUNTA($D$1:$D$9),"",SUMPRODUCT(--(ISNUMBER(SEARCH($D$1:$D$9,A2)))))

Hello Ankur Shukla

Thanks for for formula it is working well.

Also i need another 1 option in the given formula. i.e., can i change $D$1:$D$9 to $D$1:$D$20 and can i keep the $D$10:$D$20 as blank and i can the formula. If so then even if i add the new texts in the list then i no need to change the formula cells $D$1:$D$9 to $D$1:$D$20.

Thanks again for your formula.

or try below formula
b2

Please Login or Register  to view this content.

try this and copy towards down
make sure that D$1:D$9 does not blank

or try
=IF(SUMPRODUCT(COUNTIFS(A2,"*"&D$1:D$20&"*")*($D$1:$D$20<>"")),1,"")
it works with blanks too

Hello Ankur Shukla, Siva, JohnTopley

Thanks for your formulas.

All of your formulas working well.

Thanks again.