Updating "sum_range" dependent on cell value

harpzd

New Member
Joined
Dec 12, 2016
Messages
2
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,983
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

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

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,905
Office Version
  1. 365
Platform
  1. Windows
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.

Rich (BB code):
=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)
 
Last edited:
Upvote 0

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
7,006
Office Version
  1. 365
Platform
  1. MacOS

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),
 
Upvote 0

Forum statistics

Threads
1,191,191
Messages
5,985,211
Members
439,947
Latest member
fabiannic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top