automate sum that includes one more column each month

josexcell

New Member
Joined
Mar 27, 2006
Messages
39
I would do this manually when I started b/c it was just one file, now I do several and I figure there has to be a better way.

I have a budget that I have to compare against every month. It is in a spreadsheet that has January (col A) through December (col L). Each month I drop in my current month, but I have to go to my plan formula, a sum and extend it one more column to include the next month of budget.

For example, this month, i dropped in my current results for August, and my Plan is summed by month, Sum A5:H5. Next month i'll drop in Sept. results and will have to update plan, so i'll change the formula to A5:I5. I have to do this for all of the categories in the spreadsheet, 5, with 5 subcategories for each, so a lot. Is there a way to have the sum include one more column automatically?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try...

=SUM(A5:INDEX(A5:IV5,MATCH(9.99999999999999E+307,A5:IV5)))

...which will sum cells from A5 to the last cell in the row containing a numerical value.

Hope this helps!
 
Upvote 0
Two choices...

1) Just put in a sum formula that includes the whole range and then leave it alone from month to month (e.g. =sum(A1:L1) )

2) If for some reason that won't work for you, try:
=SUMIF(A1:L1,"<>""""",A1:L1)
 
Upvote 0
I have to compare Sept Plan vs. Sept results, and Sept YearToDate results, vs the YearToDate Plan. So, the sum of the Plan has to be updated as results come in so its comparing a like time period, 5 months, 6 months, etc.

Will that work?
 
Upvote 0
Can you post a sample of the data, along with actual/expected results?
 
Upvote 0

Forum statistics

Threads
1,217,317
Messages
6,135,836
Members
449,965
Latest member
Ckl43

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
Back
Top