Updating "sum_range" dependent on cell value

[harpzd]

Hi All,

Apologies if this has been answered elsewhere in some way but I've been trawling through the forum for the better part of an hour so and I'm no closer to solving my issue!

I'm in the middle of creating a dashboard to show validations/compliance fails for recent sales activity.

What I'm looking for help with is changing the "sum_range" (in a named table) in a "SUMIFS" formula dependant on the text in another cell.

For example:

=SUMIFS(data_responses[REFERENCE TO CELL],criteriarange1,criteria1 etc etc) with data_responses being my data table.

Essentially I have 7 types of "validation":

1. Data_Responses[Upfront Costs Fail]
2. Data_Responses[Setup & Contact Fail]
3. Data_Responses[PP & Contract length fail]
4. etc

If I have a cell which contains text or a formula which equals the column reference as above is there a way that I can use the cell as a reference to determine the "sum_range"?

The current formula is below:

=SUMIFS(Data_Responses[Upfront Costs Fails],Data_Responses[Month Number],BG$33,Data_Responses[Year Number],BG$34,Data_Responses[Region],G40),

BG33 is a month number
BG34is the year number
G40 is the region name

 

[Joe4]

Welcome to the Board!

When building range references dynamically for formulas, you need to use the INDIRECT function.
See: INDIRECT Function

 

[FormR]

Hi and welcome to the board.

Here is another option, where A1 is the cell reference that contains the heading for the column that you want to sum.

=SUMIFS(INDEX(Data_Responses,0,MATCH(A1,Data_Responses[#Headers],0)),Data_Responses[Month Number],BG$36,Data_Responses[Year Number],BG$37,Data_Responses[Region],G43)

 

[etaf]

you may be able to use an indirect()


=SUMIFS(Data_Responses[Upfront Costs Fails],Data_Responses[Month Number],BG$33,Data_Responses[Year Number],BG$34,Data_Responses[Region],G40),
change to
=SUMIFS(indirect(data_responses[REFERENCE TO CELL]),Data_Responses[Month Number],BG$33,Data_Responses[Year Number],BG$34,Data_Responses[Region],G40),

 

[harpzd]

Great thanks all!

I have managed to get it working using FormR's solution.

Thanks again