automate sum that includes one more column each month

[josexcell]

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?

 

[Domenic]

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]

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]

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]

Thanks for trying

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

Thanks again.

 

[Domenic]

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