CODEWITHSUNDEEP
excel
toolshed
toolshed

Reputation: 2019

Creating a dynamic range between two named ranges without VBA?

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.

Upvotes: 2

Views: 2044

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27249

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.

Upvotes: 2

Marc
Marc

Reputation: 11623

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))

Upvotes: 0

Related Questions