Hi Haseeb,
Thanks so much! This works like a charm. Exactly what I needed!


Quote Originally Posted by Haseeb A View Post
Hello Priya,

Consider these:

Data: A1:A4

A6 to down, names like apple, banana etc..

So, try this in B6, with CTRL+SHIFT+ENTER, rather than just ENTER, then copy down.

=SUM(IFERROR((LEN(", "&A$1:A$4&",")-LEN(SUBSTITUTE(", "&LOWER(A$1:A$4)&",",", "&LOWER(A6)&",","",TRANSPOSE(ROW(INDEX(A:A,1):INDEX(A:A,MAX(LEN(A$1:A$4))))))))/(LEN(A6)+3)*B$1:B$4,0))
Assume your data always be in same format. like ", " after each item