Nesting INDEX MATCH into SUMIFS

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
I currently have several formulas pulling in a SUMIFS from a data sheet, beginning with =SUMIFS('datasheet'!$N:$N, 'datasheet.....

but as I am creating a dashboard I would like to have the first variable ($N:$N) pull from a drop down list. I've created a drop down that does months and am trying to get the first variable to index match a table based on the drop down value, i.e. if it selects "November" the the INDEX MATCH will fill $N:$N for the SUMIFS formula but I'm getting errors with the placement of the apostrophe.

Old working formula that pulls only a predesignated column:
=SUMIFS('data1'!$N:$N, 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")

Nonworking formula I am trying for the first SUMIFS variable to select via INDEX MATCH to a drop down cell value
=SUMIFS('data1'!(INDEX(Sheet2!D14:D25,match(Sheet1!M2,Sheet2!B14:B25,0)), 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")

where Sheet1M2 is the drop down box and Sheet2 D14:D25 and B14:B25 are the array I'm trying to reference via drop down box
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
The first thing to note with SUMIFS or similar functions is that all ranges must be equal in size. I'm looking at your formula and a bit lost as to which parts are where at the moment.

On the assumption that column N is for november, so winding back would mean that january is in column D, try something like this one, changing the descriptions in the MATCH part as needed.

Excel Formula:
=SUMIFS(INDEX('data1'!$D$O,0,MATCH("month to look for","list of months",0)), 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")
 

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
The first thing to note with SUMIFS or similar functions is that all ranges must be equal in size. I'm looking at your formula and a bit lost as to which parts are where at the moment.

On the assumption that column N is for november, so winding back would mean that january is in column D, try something like this one, changing the descriptions in the MATCH part as needed.

Excel Formula:
=SUMIFS(INDEX('data1'!$D$O,0,MATCH("month to look for","list of months",0)), 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")
Thanks! Trying this now.

Yes, the SUMIFS ranges are equal if I'm able to get the first variable to reference an entire column - the following variables in the SUMIFS do the same, but have specific callout criteria that I can just put in quotes.

N is for November ironically, but our calendar goes Jul-Jun, so the first column to reference would be $J:$J

Trying this new formula, getting a formula error for an apostrophe again - at the $D:$O after INDEX('data1'!, though I changed it to $J:$U for my data sheet.

I may have misunderstood you regarding the MATCH("month to look for","list of months",0) - did you intend for me to use the month to look for to point to the cell with the drop down values?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
The part that I'm not following it the relevance of B14:B25 and D14:D25, those don't match up with the rest of the ranges in the sheet.

Also, as you say that column J is the first month (July), I'm not seeing how that column can also be used as a criteria with column N?

Looking at my suggested formula, I also noticed a missing : in the first range which would have contributed to the error that you encountered when you tried it.

Assuming that the criteria should actually be columns I and E, with months in J:U as you mentioned above. This should work with the month in M2 and a list of months (in order, July-June) in D14:D25.
Excel Formula:
=SUMIFS(INDEX('data1'!$J:$U,0,MATCH(Sheet1!$M$2,Sheet2!$D$14:$D$25,0)), 'data1'!$I:$I, "criteriaA", 'data1'!$E:$E, "criteriaB")
See how that goes, I'll check back in a bit to see if it worked.
 

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm so sorry I didn't explain that - B14:B25 and D14:D25 is the match list I made with B being the months as well as the reference cells for the drop down and D being the column headers from the SQL pull that fills the data sheet. I think I'm making this too difficult so I'll back it up for a sec.

=SUMIFS('data1'!$N:$N, 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")
is the original working formula. I want to use a drop down to have the $N:$N change to between $J:$J and $U:$U depending on the drop down value selected, by month beginning with July and ending with Jun. The month headers from the SQL pull for columns J-U populate as Amt 1, Amt 2, Amt 3, etc. My gut was to go with INDEX MATCH to solve this but if you have a better solution I'm all ears
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
So are you saying that July is always Amt 1, November Amt 5, etc, In order from J:U?
Excel Formula:
=SUMIFS(INDEX('data1'!$J:$U,0,MATCH(Sheet1!$M$2,Sheet2!$B$14:$B$25,0)), 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")
It might make more sense if you post a fictionalised example of your sheet(s) using the XL2BB addin (link in my signature block below) to aid in clarification of the details.
 

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So are you saying that July is always Amt 1, November Amt 5, etc, In order from J:U?
Excel Formula:
=SUMIFS(INDEX('data1'!$J:$U,0,MATCH(Sheet1!$M$2,Sheet2!$B$14:$B$25,0)), 'data1'!$J:$J, "criteriaA", 'data1'!$E:$E, "criteriaB")
It might make more sense if you post a fictionalised example of your sheet(s) using the XL2BB addin (link in my signature block below) to aid in clarification of the details.

Yes, the output data lists all values in columns J:U, with row A being the header, columns A:I and V:Z being cost centers, accounts, locations, etc.

The original formula used SUMIFS to sum all rows that meet several criteria, but uses the variable right after SUMIFS('data1'!$*:$* to chose the correct column, or month, to pull from. Works completely fine when only doing a worksheet for 1 month, but I'm trying to make a dashboard that can change which column/month the sum part is using by changing the value in a drop down.

What I had tried to do was to insert an INDEX MATCH that would reference the value in the drop down cell, and then match it to a small table (B14:B25 and D14:D25) thon my company laptopat had the months in column B and the $J:$J-$U:$U corresponding in column D, but it doesn't like how I put that in the formula.

Tried the XL2BB add in but I don't think I can manage them on my company laptop.

If my current formula won't support that, I'm fine with trashing it and going in a different direction, but the overall intent is to have cells on my dashboard view that run SUMIFS from another sheet and to be able to change the column that is being summed via drop down cell on the dashboard. If I'm not making sense let me know.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
Tried the XL2BB add in but I don't think I can manage them on my company laptop.
If security restrictions prevent the add in from being installed then uploading a sample file to dropbox or onedrive and posting a link in the thread is another option.

There is probably something simple that I'm missing but I'm out of ideas without seeing the actual layout.
 

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
If security restrictions prevent the add in from being installed then uploading a sample file to dropbox or onedrive and posting a link in the thread is another option.

There is probably something simple that I'm missing but I'm out of ideas without seeing the actual layout.
 

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows

Hopefully that works. As you can see, Sheet 1 can easily sum data from data1 column J based on qualifiers in columns I and F - cell C5 on Sheet1.

Sheet2 has what I was attempting with the drop down menu and index match - the drop down cell is C2 on Sheet1.

I'm trying to understand if there is a way to change the column being summed by the SUMIFS between columns J-U on the data1 sheet by changing what is in the drop down cell C2 on Sheet1.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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