Sum of Multiple Ranges Using Index Match With Multiple Criteria

G00DNESS

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

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,837
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,760
Office Version
  1. 365
Platform
  1. 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
59
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,130,442
Messages
5,642,140
Members
417,258
Latest member
amk1979

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top