Sum for textsplit textjoin selections from one cell

[ShanaStJ]

Need assistance with this formula. I have entered a code that is successfully working allowing multiselections for a drop down menu in one column. What I need and what is not working is the formula to total those selections individually. Below is an example and the formula I have entered that is returning a value of 0 even though there are entries. You will notice that even though there are multiple selections in the top part, they are not calulating ih the bottom. Any help greatly appreciated.

=SUM(--(TRIM(TEXTSPLIT(TEXTJOIN(",",,$U$36:$U$75),","))=U77))

 

[ShanaStJ]

What cell reference is the formula in? What cell is the line "Did not follow up with Aftercare" in?

That is in U

What cell reference is the formula in? What cell is the line "Did not follow up with Aftercare" in?

Sorry I'm trying....I can't use the xl2bb but here is another example

 

[awoohaw]

what is between U35 and U65?

why don't you post the file to a share drive?

 

[ShanaStJ]

what is between U35 and U65?

That is where someone uses the pull down menu to select multiple different items (those items are reflected to show up in a list from T77 - T86

 

[ShanaStJ]

what is between U35 and U65?

why don't you post the file to a share drive?

Unfortunately, the remainder has confidential information in it and I cannot upload it to anything due to this. I was able to put this on here with modified sample data entry in an attempt to obtain a formula for the sum. The two formula's I've used are

=UNIQUE(TRIM(TEXTSPLIT(TEXTJOIN(",",,U36:U75),,","))) This one successfully works allowing me to add a list to a pull down and I have a vba code entered so someone can select multiple entries from this list and it adds them to the same cell separated out by a comma.

=SUM(--(TRIM(TEXTSPLIT(TEXTJOIN(",",,$U$36:$U$75),","))=U77)) - This formula is the one I was trying to use to count the number of times each one of those selected from the pull down list that is in the U column.

 

[awoohaw]

what is the specific error message you get with that formula.
And I'll ask again...and make up data if you can. Please post something (text if text, numbers if number) in U36:U75. and what an expected result should be. But it appears U77 has a value of zero. So, you'll only get matches when the value is zero.

 

[ShanaStJ]

what is the specific error message you get with that formula.
And I'll ask again...and make up data if you can. Please post something (text if text, numbers if number) in U36:U75. and what an expected result should be. But it appears U77 has a value of zero. So, you'll only get matches when the value is zero.

That's just it, while I'm expecting a count of how many times that item is entered it returns zero but it does not give me an error at all.

 

[Cubist]

Try:

SplitNameEmail.xlsx
	U	V	W
66	Substance usage
67	Ran out of meds, Did not follow up with after care, Count not afford meds, Did not have transportation for aftercare
68	Did not have an appt for aftercare, Substance usage, Unstabble housing
69	Unstable housing, Refused to go to after care
70	Lack of support system, Lack of finances, Lack of finances
71
72
73
74
75		Array	Dragdown
76	Did not follow up with aftercare	0	0
77	Ran out of meds	1	1
78	Count not afford meds	1	1
79	Did not have transportation for aftercare	1	1
80	Did not have an appt for aftercare	1	1
81	Substance usage	2	2
82	Unstable housing	1	1
83	Refused to go to after care	1	1
84	Lack of support system	1	1
85	Lack of finances	2	2
Sheet6

Cell Formulas
Range	Formula
V76:V85	V76	=LET( t,TOCOL(TRIM(TEXTSPLIT(TEXTJOIN(",",TRUE,U66:U70),","))), br, BYROW(U76:U85,LAMBDA(r,ROWS(FILTER(t,t=r)))), IFERROR(br,0))
W76:W85	W76	=LET( t,TOCOL(TRIM(TEXTSPLIT(TEXTJOIN(",",TRUE,$U$66:$U$70),","))), IFERROR(ROWS(FILTER(t,t=U76)),0))
Dynamic array formulas.

 

[ShanaStJ]

That's just it, while I'm expecting a count of how many times that item is entered it returns zero but it does not give me an error at all.