Calendar Year to date

[xcelnovice]

Hi all, I have 12 months of data. The months are listed acrossed the top in columns J to U & accounts down the side in col A. I need a formula that will sum Jan-March but not include all 12 months. I’d be preparing the March report in April. Then in May I’d need it to know to pull Jan-April. I can add a date to the report if this is required. Also as a side note I plan on using this formula in a macro.

 

[eljorge]

Assuming your data looks like this

M26 contains the date you are preparing your report, and the formula below would sum up until the previous month.

The formula on M28 would be =SUM(J3:OFFSET(J3,20,MONTH(M26)-2)). Change the 20 in offset to the actual number of rows from your table. This won't work when you are on january, but you can insert an if statement around that. I'm sure there are cleaner ways to do this, but this is I could come up with.

 

[xcelnovice]

Thank you this is great! Any input on how I would tweak it to a sumif

 

[eljorge]

Thank you this is great! Any input on how I would tweak it to a sumif

Can you try this? I don't have access to a pc right now, but i think this could work:

=IF(MONTH(M26)=1,SUM(J3:OFFSET(J3,20,12)),SUM(J3:OFFSET(J3,20,MONTH(M26)-2)))

This checks if the month is january, and if so, it sums 12 columns, and if not, it will sum up until last month, like the previous formula i posted.

 

[xcelnovice]

Seems to do the trick! Many thanks! With people like you out there it’s a miracle I even have a job!