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

 

[jasonb75]

The formula in post 6 works fine when you change the dropdown from M2 to C2 and the criteria to match the current formula in the sheet.

=-SUMIFS(INDEX(data1!$J:$U,0,MATCH(Sheet1!$C$2,Sheet2!$B$14:$B$25,0)),data1!$I:$I,"AA",data1!$F:$F,"Profit")

Looking at the skeletal dashboard in sheet1, I would assume that there are further criteria to add for sales teams, etc?

 

[lost_in_the_sauce]

Yes, but I've got that part figured out as far as adding additional SUMIFS criteria, which I'm going to have to do for different group reports.

But that made everything work. The M2 was actually from before I deleted columns for the example. So in the current formula, it matches the month from the drop down against the month list and simply indexes out to the same column count (J:U) as row count (B14:B25)? I'm marking solved but I want to make sure I know what made it work.

 

[jasonb75]

So in the current formula, it matches the month from the drop down against the month list and simply indexes out to the same column count (J:U) as row count (B14:B25)?

That is correct. If you wanted to add an extra failsafe in case the order changes, you could use a second index match pair or vlookup to find the month in B14:B25 then retrieve the Amt reference from C14:C25 and search for that in row 1 of the data sheet.

For information, you could have used the references in D14:D25 to produce the formula but that would mean using INDIRECT in the formula, which is a function that is best avoided whenever there is a suitable alternative.

I was having a look at ways to get the qtr and ytd figures dynamically as well, but unless you have the IBM Summit at your disposal, I think that might be a no go.
If required, it should be doable with a few thousand rows, but the 12.5 million cells to process when using full columns will be too much.

 

[lost_in_the_sauce]

That is correct. If you wanted to add an extra failsafe in case the order changes, you could use a second index match pair or vlookup to find the month in B14:B25 then retrieve the Amt reference from C14:C25 and search for that in row 1 of the data sheet.

For information, you could have used the references in D14:D25 to produce the formula but that would mean using INDIRECT in the formula, which is a function that is best avoided whenever there is a suitable alternative.

I was having a look at ways to get the qtr and ytd figures dynamically as well, but unless you have the IBM Summit at your disposal, I think that might be a no go.
If required, it should be doable with a few thousand rows, but the 12.5 million cells to process when using full columns will be too

No, but we do have PowerBI. Maybe there's a solution with adding a column to Sheet2 that indicates what Q each month belongs in to just tell it which 3 columns to sum instead of trying to index across like the monthly formula is doing. Plently of time to look at it later, I have a lot of replicating of that formula with actual different cost centers etc before I'm ready to tackle to QTD

 

[jasonb75]

Identifying the columns is not the issue, in fact I has already changed the column refs in D14:D25 to Q1, Q2, etc in order to try a couple of methods.
The main issues is that a SUMIFS formula can only sum a single column. Summing multiple columns either means nesting offset into the formula (which is volatile and very inefficient) or using alternative array functions, sumproduct being the most common. This is less efficient than sumifs to to the way that it processes the data in the background. Often with requirements like this it is a case of figuring out which is the lesser of evils.

I did try an alternative with the filter function, but I made the mistake of doing it with full columns so my laptop decided to crash and burn like a bad actor crossing a hot desert.

Following up on what I said here

That is correct. If you wanted to add an extra failsafe in case the order changes, you could use a second index match pair or vlookup to find the month in B14:B25 then retrieve the Amt reference from C14:C25 and search for that in row 1 of the data sheet.

This is how you would incorporate that method into the formula if you wanted to.

=-SUMIFS(INDEX(data1!$J:$U,0,MATCH(VLOOKUP($C$2,Sheet2!$B$14:$C$25,2,0),data1!$J$1:$U$1,0)), data1!$I:$I, "AA", data1!$F:$F, "Profit")

This looks for the correct column in the data sheet rather than simply assuming the position.

 

[jasonb75]

A couple of extra formulas that I came up with to alleviate monday evening boredom, these are both based on the layout of the sample file that you linked earlier.
The only change that I made to the layout was to enter the qtr numbers into D14:D25 in place of the current column references.

This formula will return the info for the current quarter based on the month selected in C2.

=-SUM(FILTER(FILTER(data1!$J:$U,VLOOKUP(data1!$J$1:$U$1,Sheet2!$C$14:$D$25,2,0)=VLOOKUP($C$2,Sheet2!$B$14:$D$25,3,0)),(data1!$I:$I="AA")*(data1!$F:$F="Profit")))

This one will give year to date, from July (column J) up to and including the month selected in C2.

=-SUM(FILTER(data1!$J:$J:INDEX(data1!$J:$U,0,MATCH(VLOOKUP($C$2,Sheet2!$B$14:$C$25,2,0),data1!$J$1:$U$1,0)),(data1!$I:$I="AA")*(data1!$F:$F="Profit")))

 

[lost_in_the_sauce]

I love you.

 

[lost_in_the_sauce]

Hm. Really doesn't like the FILTER function. Wondering if being on Office 365, I don't have Dynamic Arrays in this build. I'll try my IT group.

 

[jasonb75]

If it's not allowing you to enter the formula, or it is showing a #NAME? error then that means that you don't yet have the function.

As far as I'm aware everyone should have it, but if your IT dept is a bit slow in rolling out the updates then it could be stuck in the virtual pipeline somewhere.

There are other ways that it can be done, but this seemed to process a bit more efficiently than the alternatives.

 

[lost_in_the_sauce]

Straight up not allowing entry of the formula - it only recognizes FILTERXML

I'm reaching out to my IT team to see if I can get the update pushed to me