Counting inside Cells

or simillar approach but different ..!

Cubist said:

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))

Click to expand...

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

RobP said:

or simillar approach but different ..!

Excel Formula:

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

Click to expand...

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

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.

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

banier1 said:

is it possible to achieve the solution in Google sheets?

Click to expand...

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.

Fluff said:

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.

Click to expand...

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.

banier1 said:

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

Click to expand...

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