Nesting INDEX MATCH into SUMIFS

[lost_in_the_sauce]

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

 

[Fluff]

If you get a warning like "Invalid Function" then you don't have the dynamic array functions.

 

[jasonb75]

Thanks @Fluff, it's been a while since I got the update and couldn't remember exactly what happened.

 

[Fluff]

The Filter function is an odd one, all the others give #NAME, but it won't even let you enter filter.

 

[lost_in_the_sauce]

Dang. We're on a previous OFFICE 365 build that doesn't support Dynamic Arrays, and I was told if I updated then it would mess up some of the Outlook functionality within our company network, so no luck there.

 

[jasonb75]

I don't use outlook for anything more than email basics but that sounds like a random excuse from the helpdesk handbook ?

Looking at ways to make it work as efficiently as possible.

How would you identify which quarter to show on the dashboard? Would it simply be based on the selected month in a similar way to my FILTER suggestion, or does it need to be independent of that in some way?

Are we same to assume that there will only be one year in the sheet so that ytd will always be from column J to selected month?

Are you familiar with setting up named ranges?

 

[lost_in_the_sauce]

Maybe, though my guy offered to but said I would probably run into authentication errors and have to roll back to get Outlook to get emails again. Anyway.

Thinking about adding a 4th column to the Sheet2 that gives Q1-Q4 designators to the months - if I added a row to the data sheet and had the 3 cells above Jul-Sept merged/centered with Q1 as the entry, could I just modify the existing formula to sum all 3 columns at once?

 

[jasonb75]

Unfortunately, it's not that easy.

If you merge / centre 3 cells, only the first one has value, the other 2 are useless. Merging is one of the most useless features in excel, it can ruin many perfectly good formulas.
Also, summing 3 columns with a single sumifs is not possible (without the use of highly inefficient volatile functions).
All of the ranges in sumifs must be equal in size, so if the range to sum contains 3 columns then each criteria range must also contain 3 columns with the 1st criteria column being relative to the 1st sum column, the 2nd to the 2nd, etc.

When I asked about identifying the quarter or ytd I meant how would you choose which to show in sheet 1?