Sum of Multiple Ranges Using Index Match With Multiple Criteria

[G00DNESS]

Using MS Excel 2013


Unbelievable! After I spent weeks designing a worksheet, I had to redesign it and now I'm stuck.


In Cell JO9, I'm trying to achieve the sum of the added sums in multiple [Ranges W9:W508 and AA9:AA508], using the INDEX/MATCH Method, when:
Criteria 1: IY9 [can be a duplicate] is a match anywhere in the Range AU9:AU508, and
Criteria 2: JO7 [can be a duplicate] is and match is a match anywhere in the Range X9:X508


W9:W508 and AA9:AA508 are dollar amounts
IY9 and AU9:AU508 are numeric entries
JO7 and X9:X508 are numeric entries


There will be blank cells in Columns: W, X, AA and AU.
There will be formulas in the cells of Columns: W, X, AA and AU.


Thanks for any anticipated help.

 

[Eric W]

Sounds more like a SUMIFS:

=SUMIFS(W9:W508,AU9:AU508,IY9,X9:X508,JO7)+SUMIFS(AA9:AA508,AU9:AU508,IY9,X9:X508,JO7)

If that doesn't work, a small sample with expected results would help in assisting.

 

[AhoyNC]

Try using SUMIFS function:

Excel Workbook
	A	B	O	P
1	CAT			MTH
2				JAN
3
4	BINS			5550
5	PARTS			1200
Summary

Excel Workbook
	A	D	E	K	L	M
1	MTH		CAT		AMT	CUST
2	JAN		BINS		5,000	BOB SMITH
3	JAN		PARTS		1,200	DAN PETERS
4	JAN		BINS		550	JASON BROWN
5	FEB		PARTS		250	SCOTT FINDE
6	FEB		BINS		2,500	BOB SMITH
7	FEB		MISC		150	DAN PETERS
8	FEB		PARTS		300	BOB SMITH
2019

 

[G00DNESS]

Sounds more like a SUMIFS:

=SUMIFS(W9:W508,AU9:AU508,IY9,X9:X508,JO7)+SUMIFS(AA9:AA508,AU9:AU508,IY9,X9:X508,JO7)

If that doesn't work, a small sample with expected results would help in assisting.

Ahhhhh!!! You are soooo helpfu as usual. It worked. I was struggling with that. Much thanks!

 

[G00DNESS]

Thanks for your input Ahoy! I figured it out w/ Eric's solution.

 

[Eric W]

Glad we could help!