Counting inside Cells

[banier1]

Hi There (First time post or me - please be kind )
I have a response form, from a menu survey, that I'd like to total (count) the numbers of attendees and what their choices are.
However the form design has led to multiple responses in single cells.
See Below.
Is there a way to count the instances when "Adult" is returned? Including when "Adult n, Adult n" is returned, it is counted as 2
In the end I'd like to categorise by first/second sitting and the column of response.

Please help
Kind Regards,
Dan

 

[Cubist]

Maybe this.

Book1
	C	D	E	F	G	H	I	J
1
2
3
4	8pm - Second Sitting			Adult 1
5	6pm - First Sitting	Adult 1, Adult 2					First Sitting	7
6	6pm - First Sitting	Adult 1			Adult 1		Second Sitting	8
7	8pm - Second Sitting		Adult 1, Adult 2	Adult 1			Total	15
8	8pm - Second Sitting	Adult 1, Adult 2, Adult 3			Adult 1
9	6pm - First Sitting	Adult 3	Adult 1	Adult 2
Sheet2

Cell Formulas
Range	Formula
J5	J5	=LET(f,FILTER(D4:G9,ISNUMBER(SEARCH(I5,C4:C9))),SUM(IF(f=0,0,LEN(f) - LEN(SUBSTITUTE(f, ",", ""))+1)))
J6	J6	=LET(f,FILTER(D4:G9,ISNUMBER(SEARCH(I6,C4:C9))),SUM(IF(f=0,0,LEN(f) - LEN(SUBSTITUTE(f, ",", ""))+1)))
J7	J7	=SUM(IF(D4:G9="",0,LEN(D4:G9) - LEN(SUBSTITUTE(D4:G9, ",", ""))+1))

 

[RobP]

or simillar approach but different ..!

=LET(x,TEXTJOIN(",",TRUE,D2:G2), IF(x <> "", LEN(x) - LEN(SUBSTITUTE(x,",",""))+1,0))

 

[banier1]

Maybe this.

Book1
	C	D	E	F	G	H	I	J
1
2
3
4	8pm - Second Sitting			Adult 1
5	6pm - First Sitting	Adult 1, Adult 2					First Sitting	7
6	6pm - First Sitting	Adult 1			Adult 1		Second Sitting	8
7	8pm - Second Sitting		Adult 1, Adult 2	Adult 1			Total	15
8	8pm - Second Sitting	Adult 1, Adult 2, Adult 3			Adult 1
9	6pm - First Sitting	Adult 3	Adult 1	Adult 2
Sheet2

Cell Formulas
Range	Formula
J5	J5	=LET(f,FILTER(D4:G9,ISNUMBER(SEARCH(I5,C4:C9))),SUM(IF(f=0,0,LEN(f) - LEN(SUBSTITUTE(f, ",", ""))+1)))
J6	J6	=LET(f,FILTER(D4:G9,ISNUMBER(SEARCH(I6,C4:C9))),SUM(IF(f=0,0,LEN(f) - LEN(SUBSTITUTE(f, ",", ""))+1)))
J7	J7	=SUM(IF(D4:G9="",0,LEN(D4:G9) - LEN(SUBSTITUTE(D4:G9, ",", ""))+1))

Wow that works really well, thanks. I have changed the ranges, to an per-column arrangement, and separately summing meal choices, but your formula does the job wonderfully. Thank you so much

 

[banier1]

or simillar approach but different ..!

=LET(x,TEXTJOIN(",",TRUE,D2:G2), IF(x <> "", LEN(x) - LEN(SUBSTITUTE(x,",",""))+1,0))

Thanks for helping. TBH I struggled to implement your solution, so used the solution from 'Cubist'. What is 'x' in your formula?
Cheers
Dan

 

[banier1]

To add to my request, and not sure if I'm committing a cardinal sin on this forum(!), but is it possible to achieve the solution in Google sheets? I would ideally like the solution to be in a Google Sheet as the content is generated within there.

 

[Fluff]

In future please mark the post that contains the solution, rather than your post saying it works. I have changed it for you this time. Thanks

is it possible to achieve the solution in Google sheets?

In future if you want something for Google Sheets, you should make that clear at the start & all such questions should be posted in the General Discussion & Other Applications section of the board.

 

[banier1]

In future please mark the post that contains the solution, rather than your post saying it works. I have changed it for you this time. Thanks


In future if you want something for Google Sheets, you should make that clear at the start & all such questions should be posted in the General Discussion & Other Applications section of the board.

I did hit the tick button against Cubist 's solution. Thanks.
Thanks also for your suggestion of General Discussion... section. I didn't see that when first looking at the forum.

 

[RobP]

Thanks for helping. TBH I struggled to implement your solution, so used the solution from 'Cubist'. What is 'x' in your formula?
Cheers
Dan

x is basically going to hold the "TextJoin" bit - so x = each cell in the row, joined together in 1 text string. You don't need to change that. From that text string of 1, 2 or 3 "Adult n" seperated by commas, we then remove the "," commas from it, and subtract the length of the new shorter string from the old one stored in x) (which in theory should be 1, 2 or 3 or n characters shorter). If there is 1 comma, you must have 2 Adults .. so we add "1" to each result to get you the answer ... If the string is empty ie. no Adults, we put a 0

all you had to do was amend the D2:G2 to suit the columns you were using, and copy the formula down your rows ...

Anyway - you have your solution - so all good.

cheers
Rob