Counting names in a excel cell that may contain multiple answers-CodePudding

Good Morning

I need your help with a formula please, I have a list of items in one cell:

I would like the result to say:

Apples 3
Banana 3
Pear   2
Grape  2

I have done the following =countif(Range, list) this counts all the individual items, but it won't count the items when included with other items.

Can you help please?

Thanks

Wayne

CodePudding user response：

You can use the TEXTSPLIT function to split the single column into multiple columns using the forward slash as a delimiter.

Surround the formula in the TRIM function to remove leading and trailing spaces.

=TRIM(TEXTSPLIT(A1,"/",,TRUE))

Then use the COUNTIFS function to count the results.

=COUNTIFS($B$1:$D$7,"="&B12)

CodePudding user response：

Thanks everyone, I have now figured it put with the help of JvdV.

=countif(A2:A6,"Apple")

I have added a star before and after the word Apple but its not showing in the formula

Result Apple 3

The only downside is I have to amend each cell with the word Apple or Banana etc but I am happy with this.

CodePudding user response：

Here is an option to include all unique items from your list and their count without the possibility of false positives:

Formula in B1:

=LET(x,A1:A6,y,DROP(REDUCE(0,x,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,," / ")))),1),z,MAP(y,LAMBDA(c,SUM(--(c=y)))),UNIQUE(HSTACK(y,z)))

You can include SORT() to nest UNIQUE(HSTACK(y,z)) in if need be sorted on count and/or type.