Display Number of times string from a column is present in a cell

[iip]

Lets say I have excel as below

How we can display how many words from column A (complete list ) are present each row of column c

So it should display on
Row 2 = 4
Row 3 = 3
Row 4 = 3

Also then can we show it by comparing case also
Row 2 = 4
Row 3 = 3
Row 4 = 2

Above problem is extended from Look up string in a list

 

[Fluff]

Which version of Excel are you actually using? It cannot be every version that ever existed.

 

[iip]

Which version of Excel are you actually using? It cannot be every version that ever existed.

I am using MSO 365 Excel

 

[Fluff]

In that case please update your profile to show that.

How about

=LET(a,TRIM(TEXTSPLIT(C2,",")),COUNT(MATCH(a,$A$2:$A$7,0)))

 

[iip]

In that case please update your profile to show that.

How about

=LET(a,TRIM(TEXTSPLIT(C2,",")),COUNT(MATCH(a,$A$2:$A$7,0)))

I tried about formula but answers are not as expected shown

 

[Fluff]

That's because you have moved what was in col C to col E & not changed the formula to suit.

 

[iip]

That's because you have moved what was in col C to col E & not changed the formula to suit.

Thanks a lot Fluff for responding. Actually I put the formula first and then put the 2 additional columns what i need to see. I corrected it but still same issue. I used the concept used in the topic Look up string in a list which helped me to find true/false but i am looking for counts as shown below.

 

[Fluff]

In that case can you please post some data rather than an image, as it works for me

Fluff.xlsm
	A	B	C
1
2	Cherry	4	Cherry,pineapple, Strawberry, fig
3	pineapple	3	Cherry, apple, Strawberry, fig
4	Strawberry	1	Fig
5	Guava
6	Fig
7	Melon
Main

Cell Formulas
Range	Formula
B2:B4	B2	=LET(a,TRIM(TEXTSPLIT(C2,",")),COUNT(MATCH(a,$A$2:$A$7,0)))