Sum of Multiple Ranges Using Index Match With Multiple Criteria

G00DNESS

Board Regular
Joined
Aug 6, 2014
Messages
56
Using MS Excel 2013


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


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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,555
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,476
Office Version
365
Platform
Windows
Try using SUMIFS function:

Excel Workbook
ABOP
1CATMTH
2JAN
3
4BINS5550
5PARTS1200
Summary
Excel Workbook
ADEKLM
1MTHCATAMTCUST
2JANBINS5,000BOB SMITH
3JANPARTS1,200DAN PETERS
4JANBINS550JASON BROWN
5FEBPARTS250SCOTT FINDE
6FEBBINS2,500BOB SMITH
7FEBMISC150DAN PETERS
8FEBPARTS300BOB SMITH
2019
 

G00DNESS

Board Regular
Joined
Aug 6, 2014
Messages
56
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,484
Messages
5,444,747
Members
405,299
Latest member
rcurtin

This Week's Hot Topics

Top