Dynamic Range, with Indirect?

randyharris

Board Regular
Joined
Oct 6, 2003
Messages
88
Can't figure this one out.

A1 that has a starting end of month date
A2 has an ending start of month date

Column B has a long list of end of month dates

Columns C through N have data in them related to the month end dates

I'm trying to use the value in cells A1 and A2 to dynamically adjust my formulas that look at the data in columns C through N, I'd like to be able to adjust those dates in A1 and A2 and have the formula adjust to the starting and ending rows which equal those dates. I've tried looking for INDIRECT and OFFSET to get the results, but can't get it right.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,528
It really depends on what your formulas are. Here's one possibility:

Book2
ABCDE
11/31/2020Date
210/31/20201/31/2019A1
32/28/2019B2
43/31/2019C3
5Sum4/30/2019D4
61755/31/2019E5
76/30/2019F6
87/31/2019G7
98/31/2019H8
109/30/2019I9
1110/31/2019J10
1211/30/2019K11
1312/31/2019L12
141/31/2020M13
152/29/2020N14
163/31/2020O15
174/30/2020P16
185/31/2020Q17
196/30/2020R18
207/31/2020S19
218/31/2020T20
229/30/2020U21
2310/31/2020V22
2411/30/2020W23
2512/31/2020X24
261/31/2021Y25
272/28/2021Z26
283/31/2021AA27
294/30/2021AB28
305/31/2021AC29
Sheet3
Cell Formulas
RangeFormula
A6A6=SUM(INDEX(E:E,MATCH(A1,B1:B30,0)):INDEX(E:E,MATCH(A2,B1:B30,0)))
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
Here's one possibility:
[....]
=SUM(INDEX(E:E,MATCH(A1,B1:B30,0)):INDEX(E:E,MATCH(A2,B1:B30,0)))

Or simply:

=SUMIFS(E1:E30, B1:B30, ">=" & A1, B1:B30, "<=" & A2)

PS.... Well, I only looked at Eric's example. Upon reading the OP, I think Eric's example was just over-simplified. Assuming values in columns E:G:

=SUM(INDEX(E1:E30,MATCH(A1,B1:B30,0)):INDEX(G1:G30,MATCH(A2,B1:B30,0)))
 
Last edited:

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
I've tried looking for INDIRECT and OFFSET to get the results

As Eric wrote: it depends on your formulas and, in particular, whether you need to iterate over all of the data in C:N in one formula, or your intent is to have separate formulas for each column.

The take-away is: avoid using INDIRECT and OFFSET. They are "volatile" functions. That means: any formulas that use them and formulas that refer directly or indirectly to formulas that use them are recalculated every time Excel decides to recalculate anywhere in the workbook. For example, editing. But there are operations that cause recalculations.

If you only have a few such formulas, no problem. But when you have 100s or 1000s, things get really slow. Ever see complaints from users about Excel becoming "non-responsive", or opening or saving files takes "minutes". 99 times out of 100, it is the use of "volatile" functions, especially with whole-column ranges like C:C.

Bottom line: the more specific you are in your question, with complete and concrete examples that are representative of the data and formulas, the better ours answers will be.
 

randyharris

Board Regular
Joined
Oct 6, 2003
Messages
88

ADVERTISEMENT

As Eric wrote: it depends on your formulas and, in particular, whether you need to iterate over all of the data in C:N in one formula, or your intent is to have separate formulas for each column.

The take-away is: avoid using INDIRECT and OFFSET. They are "volatile" functions. That means: any formulas that use them and formulas that refer directly or indirectly to formulas that use them are recalculated every time Excel decides to recalculate anywhere in the workbook. For example, editing. But there are operations that cause recalculations.

If you only have a few such formulas, no problem. But when you have 100s or 1000s, things get really slow. Ever see complaints from users about Excel becoming "non-responsive", or opening or saving files takes "minutes". 99 times out of 100, it is the use of "volatile" functions, especially with whole-column ranges like C:C.

Bottom line: the more specific you are in your question, with complete and concrete examples that are representative of the data and formulas, the better ours answers will be.
Index Match situation won't work as that brings a single number. I'm trying to attach an image to show it better. My FROM date is know, in this example it's always A2, and my starting sum value is B2. Based on the Through Date in D2, I need a formula that has a dynamic range from B2 through the row in column B which coinsides with the Through Date, in this example row 23.
 

Attachments

  • Screen Shot 2021-02-17 at 9.41.57 AM.png
    Screen Shot 2021-02-17 at 9.41.57 AM.png
    182 KB · Views: 3

randyharris

Board Regular
Joined
Oct 6, 2003
Messages
88
Oohhhh, I did try those index match sum formulas and they work fabulously - thanks everybody.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,528

ADVERTISEMENT

I have a few thoughts. If your start cell is always B2, you can use this formula (based on your example in post 5):

=SUM(B2:INDEX(B:B,MATCH(D2,A1:A30,0)))

Second, as joeu2004 pointed out, this is an ideal situation for SUMIFS (or even SUMIF if your version of Excel doesn't have SUMIFS):

=SUMIF(A:A,"<="&D2,B:B)


In any case, I'm glad you got it working, and that we could help! :biggrin:
 

randyharris

Board Regular
Joined
Oct 6, 2003
Messages
88
I have a few thoughts. If your start cell is always B2, you can use this formula (based on your example in post 5):

=SUM(B2:INDEX(B:B,MATCH(D2,A1:A30,0)))

Second, as joeu2004 pointed out, this is an ideal situation for SUMIFS (or even SUMIF if your version of Excel doesn't have SUMIFS):

=SUMIF(A:A,"<="&D2,B:B)


In any case, I'm glad you got it working, and that we could help! :biggrin:
I’ve implemented the solution just as you showed here with the B2:INDEX solution.

not sure I’ll change it mow to the SUMIF solution, that is clean and simple, off hand I’m unsure if Mac Excel 2019 has SUMIFS or not but how would the SUMIFS implementation work better than the SUMIF formula shown?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,528
The SUMIFS would be required if your data didn't always start at B2. You'd need 2 conditions, one for the upper bound as SUMIF has, and another for the lower bound. It would also work if your data wasn't sorted.

The formula you used is pretty clean and simple, and if you understand it, and it works for you, I see no reason to try something else. Best wishes!
 

Forum statistics

Threads
1,143,637
Messages
5,719,968
Members
422,252
Latest member
wannabegeek1

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