Use another Cell as Sumifs OR criteria

[Irene123]

Hi!

I am trying to do a Sumifs OR formula but list the criteria array in a singe cell for easy reference.

For Example, here for Rep A, I would like to show the value for USA or Mexico, and Rep B the value for Mexico Only while being able to easily update the Territory column without having to edit the formula directly. But, when referencing E2 here, it adds quotation marks so the formula being evaluated is =SUM(SUMIFS(B:B,A:A,"{""USA"",""Mexico""}"))

Is there a way to reference cell E2, without excel adding quotations marks? or another way to list the array criteria in a separate cell for the formula to reference?

			Rep	Territory	Value
Territory	Value		A	{"USA","Mexico"}	=SUM(SUMIFS(B:B,A:A,E2))
USA	100		B	{"Canada"}	=SUM(SUMIFS(B:B,A:A,E3))
Mexico	200		C	{"USA","CANADA"}	=SUM(SUMIFS(B:B,A:A,E4))
Canada	300
USA	400
Mexico	500
Canada	600

 

[Irene123]

 

[Fluff]

Hi & welcome to MrExcel.
How about like

Fluff.xlsm
	A	B	C	D	E	F
1				Rep	Territory	Value
2	Territory	Value		A	USA,Mexico	1200
3	USA	100		B	Canada	900
4	Mexico	200		C	USA,CANADA	1400
5	Canada	300
6	USA	400
7	Mexico	500
8	Canada	600
9
Main

Cell Formulas
Range	Formula
F2:F4	F2	=SUM(SUMIFS(B:B,A:A,TEXTSPLIT(E2,",")))

 

[Irene123]

THAKNY OU

Hi & welcome to MrExcel.
How about like

Fluff.xlsm
	A	B	C	D	E	F
1				Rep	Territory	Value
2	Territory	Value		A	USA,Mexico	1200
3	USA	100		B	Canada	900
4	Mexico	200		C	USA,CANADA	1400
5	Canada	300
6	USA	400
7	Mexico	500
8	Canada	600
9
Main

Cell Formulas
Range	Formula
F2:F4	F2	=SUM(SUMIFS(B:B,A:A,TEXTSPLIT(E2,",")))

THANK YOU SO MUCH!!! This works!!!

 

[Fluff]

You're welcome & thanks for the feedback.