Using Dynamic fields/values in SUMIFS

Question

I have one large DATA table that is refreshed from a SQL Server.

Example DATA table, 6 x string values and 5 x numeric values:

AREA | COUNTRY | CATEGORY | SALES GROUP | AAA | BBB | SALES $ | COSTS | VAL1 | VAL 2 | VAL 3

I have second, SUMMARY table with a number of columns that match field names in the DATA table. These are broken down so you can see summary values at different levels:

BREAKDOWN                 | SALES $ | COSTS | VAL1 | VAL 2 | VAL 3

EUROPE                      SUMIFS | SUMIFS | SUMIFS|SUMIFS|SUMIFS
 - ENGLAND
 -  - SMALL BUSINESS
 -  -  - Joe Green
 -  -  - Molly Mongers
 -  -  - Patent Felicity
 -  - CORPORATE
 -  - MAJOR
 - FRANCE
 - GERMANY
 - AUSTRIA

I've got a SUMIFS statement which I want to make more dynamic so it references column names to retrieve different data:

=SUMIFS(qryDATA[COSTS],qryDATA[AREA],"Europe",qryDATA[COUNTRY], "France")

I can change the Criteria VALUES to be dynamic thus:

=SUMIFS(qryDATA[COSTS],qryDATA[AREA],D9,qryDATA[COUNTRY],'EXEC VIEW'!AC6)

But if I try the same with SUM field or CRITERIA FIELDS, I get an error (won't accept entry {There's a problem with this formula}). AD8 contains the text COSTS:

=SUMIFS("qryDATA[" & AD8 & "]",qryDATA[AREA],D9,qryDATA[COUNTRY],'EXEC VIEW'!AC6)

How can I get the sum field name (COSTS) from the 1st/2nd examples, to be a cell reference?

Thnx

What exactly are you asking. For COUNTIFS the column needs to be selected either using the heading like you are or the range (AD1:AD100). Selecting AD8 within column CMMTD_TOTAL makes no sense — Ben Rhys-Lewis
I think he means that cell AD8 will contain a string such as "CCMMTD_TOTAL" so that the user could simply define the table field within one cell (AD8). — Jordan
@JordanBiddlecombe correct, then I can copy the formula across and it will pick up each of the headers above the formula, pulling in the respective field names in the DATA table: SALES $ | COSTS | VAL1 | VAL 2 | VAL 3 etc... — aSystemOverload

Jordan Jordan · Accepted Answer · 2016-02-09T15:51:30

Try this:

=SUMIFS(INDEX(qryDATA,0,MATCH(AD8,qryDATA[#Headers],0)),qryDATA[AREA],D9,qryDATA[COUNTRY],'EXEC VIEW'!AC6)

Using Dynamic fields/values in SUMIFS

1 Answers