# Gather a sum of cells within a user-defined range

1. ## Gather a sum of cells within a user-defined range

First time post! I couldn't find anything on here that quite matched what I was looking for, maybe I just missed it or misunderstood what I was reading...

Firstly, I'm doing this from work and cannot upload an example, sorry. I am drawing up a calculator for pre-payments. I have a chart for 24 months on X axis, Y axis has 3 layers, for 1, 2 or 3 people. Here is what I need to do put together:

1) User defines a starting month (usually the current or next month) in one cell. I have a pulldown for this.
2) User inputs number of months, usually between 2-12
3) User defines a starting balance, if any.
4) Final cell will get a total of the values of the months, starting from the user defined month (1) and counting one cell horizontally for each month, based on (2), adds Starting balance (3), and gives a total.

Problems:

I don't know the function to grab a certain range of cells along the Y axis and include them in a SUM formula.
I don't know how to get the drop down for months to convert the "month started" into a number, or to just use the month (shows as Jan, Feb, Mar, ect) in the pulldown to designate the starting month to count from.

Thanks in advance for any help!

2. ## Re: Gather a sum of cells within a user-defined range

Hi Baiken

Welcome to the forum.

I guess we can use defined name here powered by offset function.. upload a sample workbook and let me have a look in your data arrangement. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

3. ## Re: Gather a sum of cells within a user-defined range

I would happily load an example, but I'm doing this from work, and network settings will not let me upload anything

The offset function you mentioned looks like it would help, but can you elaborate on what you mean by a "defined name"?

Thanks again for the help!

4. ## Re: Gather a sum of cells within a user-defined range

Hi Baiken,

Defined name is a name which you can give to a cell / range and then it can be used in formulas as references.

I would suggest you to search internet for "defining dynamic names using offset function" and follow the logic. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

There are currently 1 users browsing this thread. (0 members and 1 guests)