# Sum of Multiple Ranges Using Index Match With Multiple Criteria

#### G00DNESS

##### Board Regular
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.

### 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
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
Try using SUMIFS function:

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

#### G00DNESS

##### Board Regular
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

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

Replies
6
Views
107
Replies
2
Views
123
Replies
4
Views
426
Replies
4
Views
104
Replies
3
Views
138

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.

### Which adblocker are you using?

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

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