automate sum that includes one more column each month

josexcell

New Member
Joined
Mar 27, 2006
Messages
36
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,696
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!
 

clayv

New Member
Joined
Sep 18, 2006
Messages
6
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)
 

josexcell

New Member
Joined
Mar 27, 2006
Messages
36
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?
 

josexcell

New Member
Joined
Mar 27, 2006
Messages
36
Thanks for trying

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

Thanks again.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,696
Can you post a sample of the data, along with actual/expected results?
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

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
Top