lookup and sum values separated by a semicolon and hashtag

[junkforhr]

Below is a smaller mock of the data I receive.

The values are separated by a semicolon and hashtag. I've found numerous formulas that will return a sum of the values in the Requests column, however, none of them seem to work if the value is not the first value in the string.

Mock of actual data:

Header​	Requests​
Families and Parents;#Young and Students;#Employment Services​	1​
Young and Students​	1​
Young and Students;#Employment Services​	1​
Older Citizens;#Families and Parents;#Disability, Sickness and Carers;#Young and Students​	4​

Desired result

Header​	Requests​
Young and Students​	7​
Families and Parents​	5​
Employment Services​	2​
Older Citizens​	4​
Disability, Sickness and Carers​	4​

 

[DanteAmor]

Try:

Dante Amor
	A	B	C	D	E
1	Header	Requests		Header	Requests
2	Families and Parents;#Young and Students;#Employment Services	1		Young and Students	7
3	Young and Students	1		Families and Parents	5
4	Young and Students;#Employment Services	1		Employment Services	2
5	Older Citizens;#Families and Parents;#Disability, Sickness and Carers;#Young and Students	4		Older Citizens	4
6				Disability, Sickness and Carers	4
Hoja21

Cell Formulas
Range	Formula
E2:E6	E2	=SUMIFS(B:B,A:A,"*"&D2&"*")

 

[junkforhr]

Try:

Dante Amor
	A	B	C	D	E
1	Header	Requests		Header	Requests
2	Families and Parents;#Young and Students;#Employment Services	1		Young and Students	7
3	Young and Students	1		Families and Parents	5
4	Young and Students;#Employment Services	1		Employment Services	2
5	Older Citizens;#Families and Parents;#Disability, Sickness and Carers;#Young and Students	4		Older Citizens	4
6				Disability, Sickness and Carers	4
Hoja21

Cell Formulas
Range	Formula
E2:E6	E2	=SUMIFS(B:B,A:A,"*"&D2&"*")

Thanks for that, it worked prefectly.