Forum Discussion
Excel formula issue - YTD comparison with function Offset
Hi Expertise,
I have a problem needed your help.
The following table with 2.5 year data from Jan 2023 to Jun 2025. The table with formula (sum & offset) to calculate the YTD of each year. When I add the data in Jun 2025, the YTD of 2023, 2024, 2025 can be automatically calculated from JAN to JUN.
I found the formula under the first column "YEAR" and the last column "YTD" as below. When I save as the file, the calculation does not work again. May I ask you comment or help in this case?
| Year | Formula | YTD | Formula | |||||||||||||||
| 2023 | =Year(Report_Date)-2 | 60 | =SUM(B8:OFFSET(A8,0,COUNT(B8:M8))) | |||||||||||||||
| 2024 | =Year(Report_Date)-1 | 120 | =SUM(B9:OFFSET(A9,0,COUNT(B9:M9))) | |||||||||||||||
| 2025 | =Year(Report_Date) | 180 | =SUM(B10:OFFSET(A10,0,COUNT(B10:M10))) | |||||||||||||||
| Year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | YTD | |||||
| 2023 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 120 | |||||
| 2024 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 240 | |||||
| 2025 | 30 | 30 | 30 | 30 | 30 | 30 | 180 | |||||||||||
Following m_tarler 's suggestion, perhaps in N7:
=SUM(B7:OFFSET(A7,0,COUNT(B$9:M$9)))
or
=SUM(OFFSET(B7, 0, 0, 1, COUNT(B$9:M$9)))
Fill down from N7 to N9.
5 Replies
For those YTD formulas I think you want the COUNT on row 10 (the 2025 year), like this:
=SUM(B8:OFFSET(A8,0,COUNT(B10:M10)))
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Hi Hans, please ignore the previous sample file. Please use this v2. Sorry for any inconvenience caused
Following m_tarler 's suggestion, perhaps in N7:
=SUM(B7:OFFSET(A7,0,COUNT(B$9:M$9)))
or
=SUM(OFFSET(B7, 0, 0, 1, COUNT(B$9:M$9)))
Fill down from N7 to N9.
Hi Hans, Thanks for your help again. Kindly find the sample file for your further study.
