how do i create a year to date formula?

[bigdan]

I'm trying to figure out how I can write a YTD formula.

Let's say that it's the month of May and I have a YTD formula in, say, Col X. X1 says the month name, May.

I'd like to add up the data for all the months so far. I can either add Q1 + Apr + May or add up the individual months.

So far I've been manually doing this every month. But is there a formula I can write that would automatically update? Ideally when I change the month name, all the YTD numbers should change. I cant figure out how to do that though.

							May
Jan	Feb	Mar	Q1	Apr	May		YTD
1	2	3	6	1	2		8

 

[Jeffrey Mahoney]

Is this what you're talking about?

Book5
	A	B	C	D	E	F	G	H
1								May
2	Jan	Feb	Mar	Q1	Apr	May	Jun	YTD
3	10	20	30	60	11	12		83
Sheet1

Cell Formulas
Range	Formula
D3	D3	=SUM(A3:C3)
H3	H3	=SUMIFS(A3:G3,A2:G2,"<>Q*")

 

[bigdan]

Is this what you're talking about?

Book5
	A	B	C	D	E	F	G	H
1								May
2	Jan	Feb	Mar	Q1	Apr	May	Jun	YTD
3	10	20	30	60	11	12		83
Sheet1

Cell Formulas
Range	Formula
D3	D3	=SUM(A3:C3)
H3	H3	=SUMIFS(A3:G3,A2:G2,"<>Q*")

Oh this is interesting! I'm going to try this out and see if it works.

This wouldn't be a full solution for me though because I'd also like to do this for the year of 2023 (I'm in Accounting, need to do a YTD for 2024 and 2023). I believe your solution is saying to look at all the numbers from Jan to Dec and exclude the Quarterly sums. In 2023 that would end up with incorrect numbers.

I wonder if there's another way around this?

 

[Joe4]

If you are going to cross years, I would highly recommend changing your values to actual dates, so you can do date math on them, i.e.
instead of "Jan", "Feb", "Mar", use dates like 1/1/2023, 2/1/2023, 3/1/2023, ... , 1/1/2024, 2/1/2024, 3/1/2024.

You can then apply a custom format on those dates of "mmm" if you still want to see it like "Jan", "Feb", "Mar".
But then at least the underlying value will be real dates that you are do real date math on!

 

[bigdan]

If you are going to cross years, I would highly recommend changing your values to actual dates, so you can do date math on them, i.e.
instead of "Jan", "Feb", "Mar", use dates like 1/1/2023, 2/1/2023, 3/1/2023, ... , 1/1/2024, 2/1/2024, 3/1/2024.

You can then apply a custom format on those dates of "mmm" if you still want to see it like "Jan", "Feb", "Mar".
But then at least the underlying value will be real dates that you are do real date math on!

Unfortunately these reports have existed for a long time and I wont be changing that part of the convention now.

The way it works at the moment (I'm just checking right now) C11 says "F2024", C12 says "Nov". D11 and D12 would say F2024 and Dec, etc.

 

[Joe4]

Unfortunately these reports have existed for a long time and I wont be changing that part of the convention now.

It is so hard to work with poorly formatted data!
You seem to have your work cut out for you!

 

[Jeffrey Mahoney]

In my example above, you could add years above the months, then add another qualifier in the SUMIFS to include the year you need.