YTD total at change of year

[Heimhenge]

Greetings All, new to this forum and first post. Been coding Excel formula's for 20 years and this one has me stumped. Coming to the experts for some help. TIA.
Trying to do a YTD total for rainfall. Using this formula:

=IF(N19=0, "",

IF(YEAR(A19)<>YEAR(A18), "",

IF(N19=0, O18,

SUMIFS(N:N,A:A, ">="&DATE(YEAR(A19), 1, 1),A:A, "<="&A19))))

Generates good numbers in most cases. See attachment excel-1.jpg.

Column N is where I enter the weekly rainfall total from my rain gauge. Column O shows the YTD total rainfall.

The glitch occurs when it's the first week of the year AND there is a non-zero rainfall to enter in comun N.

See attachment excel-2.jpg.

I'm thinking I might need an IF/AND expression but can't find the right code. Any help is most appreciated.

-Dan

 

[myall_blues]

Maybe this?

Regards

Murray

Book1
	A													N	O
1														Rain	YTD
2	28/12/2023													1	1
3	29/12/2023														1
4	30/12/2023													3	4
5	31/12/2023														4
6	1/01/2024													2	2
7	2/01/2024													5	7
8	3/01/2024														7
9	1/01/2025														0
10	2/01/2025														0
11	3/01/2025													2	2
Sheet1

Cell Formulas
Range	Formula
O2:O11	O2	=IF(YEAR(A2)<>YEAR(A1),N2,SUM(O1,N2))

 

[footoo]

=IF(YEAR(A17)<>YEAR(A16),B17,B17+C16)

 

[Heimhenge]

Thanks for the suggestions, but I don't see how to implement them w/o creating additional problems.

myall_blues: I see that works perfectly in your spreadsheet, but don't see where it should go in my existing formula. There are four lines, each addressing some aspect of the YTD calculation. If you're suggesting it to replace the existing second line, the last two lines won't be executed.

footoo: I see the same issue with your suggestion.

 

[myall_blues]

It should replace your existing formula entirely.

 

[Heimhenge]

It should replace your existing formula entirely.

Wow! You are right. When I tried it I got all the correct numbers, including those at the change of the year (not shown in attached image). Samw with the formula suggested by footoo. Both removed some of my preferred formatting though ... like when there's no rain in column N, I want a blank cell in column O just to reduce visual clutter and not be redundant. I could probably work that out on my own at this point, but you're obviously the experts, so I'll ask how you would do that? TIA!

 

[myall_blues]

Because of the way the running total works I'd suggest that rather than try to blank it out using the formula, you do it with conditional formatting by setting the text colour to white when the corresponding cell in column O is blank. Like so:

Book1
	A	B												N	O
1
2	28/12/2023													1	1
3	29/12/2023														1
4	30/12/2023													3	4
5	31/12/2023														4
6	1/01/2024													2	2
7	2/01/2024													5	7
8	3/01/2024														7
9	1/01/2025														0
10	2/01/2025														0
11	3/01/2025													2	2
Sheet1

Cell Formulas
Range	Formula
O2:O11	O2	=IF(YEAR(A2)<>YEAR(A1),N2,SUM(O1,N2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O2:O11	Expression	=ISBLANK(N2)	text	NO

Regards

Murray

 

[footoo]

...

 

[Heimhenge]

Never used conditional formatting for cells but found another way to get the same result. Since column O now has the correct values, I simply changed it a helper column and hid it. Then I created a new column P for the YTD values and used this formula:

=IF(N19=0,"",O19)

Works perfectly now. Thanks for all the help. I'm gonna mark your equivalent solution as the answer to my question.

 

[myall_blues]

Glad you got it working. Thanks for the feedback.