Sum multiple territories referencing one cell

[daddyfats]

Hi there, I run sales operations and trying to figure out a formula that can look at multiple territories for a rep that are contained in one cell. I can separate the territories however necessary (a common or slash or semi-colon). I want to be able to look up 1,2,3 or more territories and then sum the quarterly quota for those territories. I have attached an example. Any help would be so great! I cannot figure it out. I have searched the forum and online. And if there is only 1, still return just that figure.

I have a screenshot below showing the yellow cells I am trying to solve for.

In short - go look up the territory in my data and summarize the total quarterly quota. So in the screenshot below I3 would give me $30, J3 would give me $30.

I really appreciate any help!

Andrew

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you are after?

BTW, you can help your helpers by providing sample data in a form that can easily be copied for testing. Have a look at XL2BB

23 12 19.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Territory	Q1'24	Q2'24	Q3'24	Q4'24			Territory	Q1'24	Q2'24	Q3'24	Q4'24
3	West	50	50	20	40			West,South	70	90	60	70
4	South	20	40	40	30			West	50	50	20	40
5	North	40	20	30	50			West,South,North	110	110	90	120
Sum territories

Cell Formulas
Range	Formula
I3:L5	I3	=SUM(FILTER(B$3:B$5,ISNUMBER(MATCH($A$3:$A$5,TEXTSPLIT($H3,","),0))))

 

[daddyfats]

Thank you Peter! I wasn't sure how to upload a mini-sheet, but point taken for my next post! I will try this formula!