SUMIFS and non-volatile alternative to INDIRECT

[carteBlanche]

Hi everybody,

I have a workbook with a data set and a relatively large report. The raw data are pulled from some ERP software and stored in a table in a worksheet. Then, I use a combination of SUMIFS and INDIRECT functions to organise the data in a report in another worksheet.

Basically, INDIRECT is used within SUMIFS (as its first argument) to refer to the desired column of the raw data table. The columns of the table with the raw data as well as their order change more often than not each time I output the data from the ERP. I like the flexibility offered by INDIRECT for that reason.

I've used this method with smaller reports but I knew I'd have to find a non-volatile alternative to INDIRECT for larger reports. So here I am, my workbook is painfully slow and I am stuck after hours of search on the internet.

I've read about alternatives using CHOOSE/MATCH or named ranges but never managed to make something of it without using INDIRECT at some point.

I've used INDIRECT for way too long and probably can't open my mind to something different or another approach to this, so any pointers would be much appreciated

Please note that:
- I do not use pivot tables on purpose because of the data structure in the ERP software;
- I did not explore VBA-oriented options on purpose as well because the workbook is used by many people;
- I'd like to avoid having to set calculations to manual;
- I could re-organise the structure of the output data in the ERP (and have a single column with amounts, thus eliminating the need to refer to the desired column) but that would result in a table with way too many rows.

Thanks,

Tom.

 

[carteBlanche]

Thank you very much!

@oldbrewer: my goal is to get the number only of the selected scenario for item1, 2015, annual, quantity. I had tried SUMPRODUCT before without much success.

@Aladin: a virtual beer for you! I use heavily INDEX/MATCH and yet I'd never thought of using it right inside SUMIFS.

For future reference, my goal was to come up with a formula that would somewhat add horizontal lookup capabilities to a SUMIFS function. Using an INDIRECT function as the first argument of the SUMIFS function is an option. However, because INDIRECT is a volatile function (see this thread and here), the more it is used, the poorer the performance of your workbook gets. Therefore, I was looking for a non-volatile alternative to INDIRECT.

Thanks to Aladin, here is the final formula:

=SUMIFS([I]INDEX(tblDATA;;MATCH($D$14;tblDATA[#Headers];0)[/I]);
tblDATA[range];criterion;
tblDATA[range];criterion;etc.)

instead of:

=SUMIFS([I]INDIRECT("tblDATA["&$D$14&"]")[/I]);
tblDATA[range];criterion;
tblDATA[range];criterion;etc.)

Hope this can help other.

Cheers,

Tom.

Edit
@Rory: thanks also!

 

[carteBlanche]

Thanks Rory

 

[gshiels]

This thread was a lifesaver, so thanks very much.
I didn't know that some countries use a semi-colon as a separator, and other use a comma. Once I figured out that I should use comma's instead of semi-colon's, the formula worked like a dream.