Excel INDEX MATCH COUNTIF-CodePudding

I know this is very simple, but I'm quite stuck on that. I have a very simple file with the following columns:

I need to count how many people have chosen the fruit specified in column C. I tried to do something like

=INDEX($B$1:$B$8,MATCH("*"&$C1&"*",$A$1:$A$8,0))

And it correctly finds the first person corresponding to a given fruit, but how to make it count the total number of people? I've tried using countif, but I'm missing something...

CodePudding user response：

If you want to be sure to not match a substring, like count "apple" in "pineapple", you'll have to cover several cases:

when the word occurs at the start of the list
when the word occurs at the end of the list
when the list only has that word
when the word occurs anywhere else in the list

So then the formula for cell C1 becomes:

=COUNTIF(A:A,"*, "&C1&",*")  COUNTIF(A:A,C1&",*") COUNTIF(A:A,"*, "&C1) COUNTIF(A:A,C1)

This assumes the comma separated list always has exactly one space after the comma, and none before it.

CodePudding user response：

=COUNTIF(A1:A8,"*apple*")

and if you want to use the cell as a reference

=COUNTIF($A$1:$A$8,"*"&C1&"*")