Count How Many Times Comma Separated Letters In a String

[Legacy 143009]

I have a list of letters in a single cell in form of comma separated values. (i.e a,b,c,d)
What I want to check is, how many times any of these letters appear in a cell value. (ie. "Carla" has 2 'a's and 1 'c', so desired result should be 3).

Thanks for help!

 

[JvdV]

For Excel 2019, you could try:

=SUMPRODUCT(LEN(B1)-LEN(SUBSTITUTE(LOWER(B1),FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s"),)))

Where A1 holds the comma-seperated values and B1 is "Carla". Note that I assume lowercase letters thus we need to include LOWER() since SUBSTITUTE() is case-sensitive.

For ms365 users this could be written like:

=SUM(LEN(B1)-LEN(SUBSTITUTE(LOWER(B1),TEXTSPLIT(A1,","),)))

 

[Legacy 143009]

For Excel 2019, you could try:

=SUMPRODUCT(LEN(B1)-LEN(SUBSTITUTE(LOWER(B1),FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s"),)))

Where A1 holds the comma-seperated values and B1 is "Carla". Note that I assume lowercase letters thus we need to include LOWER() since SUBSTITUTE() is case-sensitive.

For ms365 users this could be written like:

=SUM(LEN(B1)-LEN(SUBSTITUTE(LOWER(B1),TEXTSPLIT(A1,","),)))

OK,

SUM() function did the trick. I don't know how I didn't come with it Thanks a lot