automate sum that includes one more column each month

josexcell

New Member
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!

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)

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?

Thanks for trying

Thanks for trying, I guess maybe its just a manual thing.

Thanks again.

Can you post a sample of the data, along with actual/expected results?

Replies
1
Views
289
Replies
13
Views
369
Replies
3
Views
177
Replies
1
Views
151
Replies
3
Views
205

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.

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

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