Creating a dynamic range between two named ranges without VBA?

2
votes

I am trying to create a dynamic range using Excel functions that will sum values between the two named ranges.

This is similar to creating a table in 2010, whereby you can sum all additions to the table.

My issue is that I do not want to create a table. I would simply like to specify a start and ending point and create the range from there.

I tried separating two named ranges by a colon, i.e. rng1:rng2, but this does't work the same way as using two absolute cell references like A1:C16. Instead, it selects the two ranges individually, not a range bounded by the two.

Is there any way to accomplish this?

Currenlty, I use dynamic ranges using the OFFSET and COUNTA functions, but these will only work if you are interested in an entire row/column or a specific range.

2
excel
this is a little confusing to me "I am trying to create a dynamic range using Excel functions that will sum values between the two named ranges." A dynamic range simply is a fancy way to set a range definition - as you know via offset and counta or match, index or whatever else you may use. When you say you want to sum values between 2 named ranges, I think of =sum(rng1,rng2). I know you want something different, but can you be more clear? Can you post a workbook with what you are looking to do? – Scott Holtzman
=sum(rng1, rng2) will simply sum the two individual named ranges, not the values between them. If rng1 is defined as cell A1, and rng2 as cell A17, =sum(rng1,rng2) will ONLY sum the value in cells A1 and A17, NOT A1 THROUGH A17. Cells A2-A16 will not be included, that's my issue. – toolshed
see my answer below. You need to name the range you are referring two, then it will sum the cells in that range with whatever else you are summing it against. – Scott Holtzman

2 Answers

2
votes

On the contrary to Addikt's last comment summing 2 defined ranges does not sum only their upper left most value.

Answer is here:

Name rng_1 and refer it to H13:H16
place the number 1 in all cells in that range

Name rng_2 and refer it to I13:I14
place the number 2 in all cells in that range

In cell K13 type = sum(rng_1,rng_2). Answer will be 8, not 3.
0
votes

Completely wild guess based on a lack of clarity in your question, but let's say you have a series of numbers in column A. You could "dynamically" sum numbers in row 1 through n as follows:

=SUM(A1:INDIRECT(B1))

Where cell B1 contains a value like "A10" (this would sum cells A1:A10).

Change the cell named in b1, and your sum formula will update accordingly. You could also specify the full range in B1, i.e. if cell B1 contains "A1:A10":

=SUM(INDIRECT(B1))