CODEWITHSUNDEEP
excelsumifs
aSystemOverload
aSystemOverload

Reputation: 3074

Using Dynamic fields/values in SUMIFS

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

Upvotes: 1

Views: 386

Answers (1)

Jordan
Jordan

Reputation: 4514

Try this:

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

Upvotes: 1

Related Questions