How to create a rolling 12-month total for this Excel sheet?

Question

How to create a rolling 12-month total for this Excel sheet?

Seth 40

This is an attendance sheet with a sum total of the rows on the "Points balance" column first column on the left. The dates are above each subsequent column where points are added.

The name of associate and their points are recorded in the row.

How do I make points drop off 12 months after they are inputted, so the Points balance column updates the total?

Ex: Receive 2 points on Feb 2nd 2025, and remove 2 points on February 2nd 2026

Thanks

User's image

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

JeovanyCV 470 Volunteer Moderator

Hi Seth

To find a solution to your problem,

I created a 6-year (1st-Jan-2025 to 31 Dec-2030) Attendance Template, based on the file you shared.
To facilitate navigation through the 2000-plus dates/columns in the table to enter the data, I created formula Hyperlinks that will take you to the Beginning or the End of the current month.
By clicking the links, you can also go to the current Date column or go to the column matching the date you enter in cell A18.

User's image

Here is the working formula for the Point Balance column.

=SUMIFS($G5:$CFM5,$G$3:$CFM$3,">"&EOMONTH(TODAY(),-12),$G$3:$CFM$3,"<="&TODAY())

You may find this solution file at this link.

https://1drv.ms/x/c/0690c2e5540f9131/ET9UJlu07E9Hg48dmXNmeVYBvMv772Hue6kceInqDFg1Xw?e=iYxwk1

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Seth 40 Reputation points

2025-10-30T11:22:52.1766667+00:00

Thank you for your help I appreciate it!
JeovanyCV 470 Reputation points Volunteer Moderator

2025-10-31T03:14:02.7066667+00:00

Hi Seth

I'm glad the solution helped you.

Thank you for the positive feedback

Kind regards

Jeovany

Answer 2

Hendrix-C 16,155 Microsoft External Staff Moderator

Hi @Seth,

Thank you for posting your question in the Microsoft Q&A forum.

Based on your sharing, you want the Points Balance columns will update automatically by removing points exactly 12 months after they were added. To achieve this, you can use a SUMIF formula to sum only points within the last 12 months relative to a reference date.

This is the current setup in my test environment. In cell B3, I use this formula: =SUMIFS(C2:N2,$C$1:$N$1, ">"&EDATE($O$2, -12),$C$1:$N$1, "<=" & $O$2)

User's image

When the reference date changes to 1/1/2026, the Point Balance will be dropped off automatically after exactly 12 months.

User's image

Hope this will help resolve your concern. Please don't hesitate to reach out again if you need help with any steps. I'm here to support you and make sure everything will work properly.

Thank you for your understanding and cooperation. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Seth 40 Reputation points

2025-10-27T17:12:03.4733333+00:00

Thank you for this explanation. How do you create a reference date? Would you mind taking a look at my spreadsheet?
Hendrix-C 16,155 Reputation points Microsoft External Staff Moderator

2025-10-27T17:56:02.31+00:00

Hi @Seth,

I tried to get in your sharing but unfortunately, I don't have permission to access your link. Could you please share me the permission so I can take a look at your spreadsheet and help you with your concern?
Thank you for your cooperation and understanding. Hope to hear from you soon.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Seth 40 Reputation points

2025-10-27T18:06:06.1133333+00:00

I am looking to apply this to all rows with names.
Hendrix-C 16,155 Reputation points Microsoft External Staff Moderator

2025-10-27T18:54:25.97+00:00
Hi @Seth,

Thank you for your sharing. With your datasheet, there are two options you can consider:

Option 1: Check with manual-added date

If you want to check with different date whenever you want, you can use this formula:

=SUMIFS(C4:AID4,$C$2:$AID$2, ">"&EDATE($B$1, -12),$C$2:$AID$2, "<=" & $B$1)

You will change the date in cell B1 manually to which date you prefer. In my previous reply, I also added the reference date manually so I can check at any time I want. You can add the reference date in any cell you want and then change the value of $B$1 in the formula. For example:

Option 2: Data gets updated daily

If you want the Point balance update automatically, you can use this formula:

=SUMIFS(C4:AID4,$C$2:$AID$2, ">" & EDATE(TODAY(), -12),$C$2:$AID$2, "<=" & TODAY())

By using TODAY(), the Points Balance will update daily automatically.

To apply this formula to all rows, you just need to copy cell B4 and then paste to all the cells that you need to calculate Points Balance.

You can try workaround and reach out again if you have any troubles or need explanation in anything. I'm happy to help.

Thank you for your cooperation an understanding. Hope to hear from you soon.
Seth 40 Reputation points

2025-10-27T19:29:54.3733333+00:00

Thank you I will try to work on this and get back to you tomorrow
Ashish Mathur 101.8K Reputation points Volunteer Moderator

2025-10-27T23:10:12.4033333+00:00

Hi,

Not clear about your question. In that file you shared, please show the expected result.
Seth 40 Reputation points

2025-10-28T11:27:25.2866667+00:00

Hi Ashish,

The first column titled "Points Balance" is currently set up to total all numbers entered into that row.

I am looking to modify the formula to sum, but also remove points added exactly 1 year after they were inputted.

Ex: March 1 2025: Bob gets 2 points. May 1 2025: Bob gets 4 points. His total is 6 points as of May 1 2025.

March 1 2026: Bob loses 2 points because it's 1 year since he received the points on March 1 2025. His points total is 4 points as of March 2 2026.
Hendrix-C 16,155 Reputation points Microsoft External Staff Moderator

2025-10-28T23:12:56.17+00:00

Good day @Seth,

Hope things are running smoothly on your end.

Following up on the support thread we've been working on. If you are still facing the same issue or need assistance with anything else, kindly respond to this email, and I will be happy to help.

We want to make sure everything is working as expected and that your experience remains uninterrupted.

Thank you for your patience and understanding throughout the troubleshooting process.

I look forward to hearing from you soon.
Ashish Mathur 101.8K Reputation points Volunteer Moderator

2025-10-29T23:10:21.2966667+00:00

Share the download link of the file and show the expected result very clearly. The link you shared earlier takes me to a log-in page.
Seth 40 Reputation points

2025-10-30T11:22:43.7366667+00:00

Thank you for your help I appreciate it!
Hendrix-C 16,155 Reputation points Microsoft External Staff Moderator

2025-10-31T20:47:52.2066667+00:00

Hi @Seth,

I'm glad to hear that the solution could help resolve your concern.

Please feel free to reach out anytime if you have any questions or need assistance. We're happy to help.

Have a nice day!

Share via

How to create a rolling 12-month total for this Excel sheet?

0 additional answers

Your answer