Excel 2010, increasing multiple formula areas

[FuzzyApocalypse]

I am working with an Excel 2010 file and am not sure how to approach my problem.

Example:
Cell A1 has the formula =SUM(B1:B3).  Cell A2 has the formula =SUM(C1:C3). Increase the formula area for A1 and A2 to include B4 and C4 respectively. Then include B5 and C5, and so on. What I need is a way to adjust both formulas on an ongoing basis while only editing one cell each time.

In reality, I am working with 50+ SUM() formulas, spread all over a worksheet, pulling data from multiple sheets, and that need to be updated on a monthly basis. This makes clicking on each formula and increasing the last value (X1:X#) by 1 very tedious and time consuming. If someone can explain how to approach the simple example above, I should be able to apply it to my specific situation. I will be extremely grateful to anyone that can help in this regard. Thank you.

[TechnoGeek]

Empty cells count as 0 for the sum formula last I checked, so you can probably just use B1:B100, C1:C100, etc. and not have to update it for another 100 months or so. Ranges also auto-expand in some cases, so if you right-click -> insert column before the last column in your range, you can see if the formula has automatically expanded by one column.

[Salmon Trout]

To sum a column in Excel you can use the SUM formula e.g. =SUM(C1:C25) with the final cell row number (25 here) being the bottom-most cell that is to be included.

As you note as the lowest cell keeps moving down you have to change that number e.g. from 25 to 26, 27 etc.

Alternatively as Technogeek noted you can just make the number so big* that you will never need to change it (or not for a long time).

But if the sum range starts at row 1, and you want the sum of the whole column, you can just use the column letter in the formula e.g. in our case =SUM(C:C) noting that this formula will give a circular reference if it is itself in column C.

*I just noticed that if you enter =SUM(C1:C65536) in Excel 2003 it gets changed by Excel to =SUM(C:C) anyway. 65536 is the maximum number of rows in Excel 2003. It is 1048576 in Excel 2007 onwards so if you try that higher number in Excel 2010 you should find the same thing happening.

Of course if the first cell row in the sum range is not 1 you will have to state the row numbers explicitly but you can still make the final cell the highest number possible for your Excel version. (I think there might be problems - #NAME? errors - using numbers 65537 and up if the spreadsheet was opened in older Excel versions like 2003)

[Salmon Trout]

It is probably more concise to say that typing a cell range C:C in a formula is equivalent to typing C1:Cmax where max is the maximum number of rows in your Excel version.

[oldun]

You could include the OFFSET and COUNTA functions in your formula.
For example, in A1 and A2, respectively, enter:

Code: [Select]

=SUM(OFFSET(B1,0,0,COUNTA(B:B),1))
=SUM(OFFSET(C1,0,0,COUNTA(C:C),1))


[FuzzyApocalypse]

TechnoGeek and Salmon Trout, thank you, but that will not work.  Let me clarify what I am doing.

I am running year to year comparisons of attendance at my organization for year to date, updated monthly.  So for example, I am comparing data from January to February of 2013 with data from January to February for 2012, 2011...1997.  At the end of March I need to change this it be a comparison of January to March for all the relevant years. All the data from previous years is already entered in the spreadsheets. I am working on macros to decrease the time it takes to update these comparisons. 

What I don't know how to do is make a macro that increases the formula area by 1 cell so that I can change my monthly comparisons with the click of a button.

If we go back to my original example:

Example:
Cell A1 has the formula =SUM(B1:B3).  Cell A2 has the formula =SUM(C1:C3). Increase the formula area for A1 and A2 to include B4 and C4 respectively. Then include B5 and C5, and so on. What I need is a way to adjust both formulas on an ongoing basis while only editing one cell each time.

Pretend that cells B4:B12 and C4:C12 are not empty. They already have data in them that I just do not want to have included in the formula yet.

Oldun; I tried your formulas, but either they are an extension of what TechnoGeek and Salmon Trout were saying, or I am missing something.  If this formula can accomplish what I described above, let me know and I will play around with it some more.

Thank you for all your help!

[oldun]

As you are expanding the range based on the current month, try this:

1. Select Define Name, enter a name for column B (e.g. ColB), and in the Refers to box, enter:
  '=OFFSET($B$1,0,0,MONTH(TODAY()),1)' without quotes
2. Repeat 1 above for column C, changing the name (e.g. ColC) and the formula
   (change $B$1 to $C$1)
3. In A1 of the worksheet, enter:
   '=SUM(ColB)' without quotes. In A2, enter: '=SUM(ColB)' without quotes.

[FuzzyApocalypse]

That looks good! But is there a way to lock the date in place? Each month I need to have a saved copy with data up to that point that won't change if I open it later.

[TechnoGeek]

Instead of MONTH(TODAY()), use MONTH($R$C), where $R$C is a cell you choose to contain the date in question.