Sumifs from the List of criteria

[p9j123]

I need help in creating a sumifs formula, but instead of the criteria being static or from a single cell, I need the sumifs formula to check on the list of criteria.

I need a formula in excel specifically in B1 of Sheet1, to sum the value of Sheet2!L:L if

1.) Sheet2!J:J contains any of the content from Sheet1!I:I and,
2.) Sheet2!A:A contain any of the content from Sheet1!K:K and
3.) Sheet2!M:M is equals to "Chicago"

 

[awoohaw]

Is this some kind of excel challenge question that you're trying to win a prize with. I'm sure there are some shorter formulas than this. But this seems to work. I did not create multiple sheets, so you'll need to adjust the formulas as necessary:

NOTE: I am assuming you want to sum of all regardless of the fact that you may have only 1 match in each of the 3 criteria (and that could even be in one row).

Book1
	A	B	C	D	E	F	G	H	I
1		526		Sheet1I	Sheet1K	Sheet2A	Sheet2J	Sheet2M	Sheet2L
2				TGQ	JFT	UXV	OZX	Chicago	31
3				IQZ	IQZ	BMS	VOW	Chicago	29
4				OJE	VQK	IQZ	LOI	Chicago	32
5				KIJ	ZWD	NQE	BER	New Orleans	36
6				SCK	IHB	LOJ	OCY	New Orleans	30
7				DKY	FTF	HDI	XER	New Orleans	39
8				YJJ	WRH	TQL	KJZ	New Orleans	30
9				HMD	UZK	BWX	OUQ	Chicago	39
10				KJP	HTO	KDF	ZCU	Chicago	38
11				OVI	IUU	WGK	HEG	Chicago	39
12				LYS	YQA	XDZ	ISB		42
13				WLH	WJR	EVJ	OFK		35
14				LND	YJK	KED	ZBV		42
15				IBK	PUI	XFQ	WJR		37
16				LWB	IZV	FSL	OYQ		27
17				YDN		JZL
18				EMW		GCF
19				HTU		WFZ
20				LOI		BEV
21				GWG		VHG
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IF(AND(SUM(--(TRANSPOSE($D$2:$D$21)=($G$2:$G$16)))>0, SUM(--(TRANSPOSE($F$2:$F$21)=($E$2:$E$16)))>0, SUM(--($H$2:$H$11="Chicago"))>0),SUM($I$2:$I$21),0)