Calculate sum of sales for the past 12 months

Hi all,

I am trying to find a formula that its going to help me with my work.

I have a sheet, with sales for the 12 months from last year + 12 months from this year.

Is there any way to make a formula that will count the current month sales + the past 11 months ?


Thank you all

Try,

Please Login or Register  to view this content.

Thank you very much. I think it worked, but could you tell me, when month July comes, is it going to start counting from July ?

Thank you

Thank you josephteh. It will work, but the only issue I may face is if I have 0 sales in June, and 0 sales in July and let say I start counting from August, where I have 10 sales. I beleive it will start counting from June since, we have 0 sales there.
Otherwise the formula works like a gold.

Thanks

Originally Posted by josephteh

Try,

Please Login or Register  to view this content.

Thank you josephteh. It will work, but the only issue I may face is if I have 0 sales in June, and 0 sales in July and let say I start counting from August, where I have 10 sales. I believe it will start counting from June since, we have 0 sales there.
Otherwise the formula works like a gold.

Thanks

Originally Posted by 6StringJazzer

Not sure who you are asking.

My solution that redesigns your data will start counting back 12 months including the current month. So yes, in July it will start counting in July.

josephteh's solution finds the first month with a 0, then counts backwards 12 months before that.

Hi,

Thank you very much for the formula, I think this is the best solution, but here comes the issue, that I am pretty sure it may be fixed.
I see from your file, you have made a custom format. Could you please tell, which one did you use? Also did you type 1/1/2022, 2/1/2022, 3/1/2022 etc ?

Thank you very much again, you guys are majsetics

Are you still using Excel 2016?

Originally Posted by josephteh

Are you still using Excel 2016?

Hi,

I beleive the version is 2022. It does say Version 2208 ( Build 15601..)

I think your Excel version is 365. Try, =SUM(OFFSET(Table1,0,MAX(XMATCH(FALSE,Table1[[Last Jan]:[This Dec]]="",0,-1)-12,0),1,12)), with blanks (instead of 0) in coming months.

Thank you all for the help.

The best solution, that worked for me was the suggestion from 6StringJazzer.
The other formulas work also as well.