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.

 

[oldbrewer]

so show us what the raw data looks like AND what it looks like in the table in the worksheet (anonymise if necessary)

 

[carteBlanche]

Here is what the raw data look like in Excel.

Basically, I use INDIRECT to refer to scenario A, B, C or D.

 

[RoryA]

What's your current formula?

 

[carteBlanche]

The current formula in the report would be ("tblDATA" being the name of the table):

=SUMIFS(INDIRECT("tblDATA["&D$3&"]");tblDATA[VAR A];$C10;
tblDATA[VAR B];D$2;
tblDATA[VAR C];D$3;
tblDATA[VAR D];D$4)

 

[oldbrewer]

do u want, say, for item 1 , 2015, quantity, the 4 numbers in the scenario columns..........

 

[oldbrewer]

if so sumproduct looks a possibility....

 

[oldbrewer]

												quantity
														sca	scb	scc	scd
											item1	2015	annual	9
item1	2015	annual	quantity	3	4	5	6
item1	2015	annual	netsales	4	5	6	7
item1	2015	annual	prodmar	5	6	7	8					formula for 9 is
item1	2015	annual	quantity	6	7	8	9
item1	2015	annual	netsales	7	8	9	10		=SUMPRODUCT(($A$31:$A$43=$L29)*($B$31:$B$43=M29)*($C$31:$C$43=N29)*($D$31:$D$43=$M$27)*($E$31:$E$43))
item1	2015	annual	prodmar	8	9	10	11
item1	2014	annual	quantity	9	10	11	12
item1	2014	annual	netsales	10	11	12	13
item1	2014	annual	prodmar	11	12	13	14
item1	2014	annual	quantity	12	13	14	15
item1	2014	annual	netsales	13	14	15	16
item1	2014	annual	prodmar	14	15	16	17
item1	2014	annual	quantity	15	16	17	18

<colgroup><col width="64" span="21" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

The current formula in the report would be ("tblDATA" being the name of the table):

=SUMIFS(INDIRECT("tblDATA["&D$3&"]");tblDATA[VAR A];$C10;
tblDATA[VAR B];D$2;
tblDATA[VAR C];D$3;
tblDATA[VAR D];D$4)

Try to replace INDIRECT with INDEX and you are good to go. If you don't how, try to post your exhibit in an Excel readable form.

 

[RoryA]

It will be:

=SUMIFS(INDEX(tblData;0;MATCH($D$3;tblData[#Headers];0));tblDATA[VAR A];$C10;
tblDATA[VAR B];D$2;
tblDATA[VAR C];D$3;
tblDATA[VAR D];D$4)