Formula for running six-month total points for many employees

Lori Flanigan

I am tasked with designing a sheet to track points that employees (~250) accrue daily (Sunday-Saturday). Point categories are 0.25, 0.50, 0.75, 1.00, and 2.00. The running points total needs to be over six months. I'm not great at formulas and from what I've seen in other Community posts about running six-month totals, the year change from December to January makes me not even try it. LOL

This is my sheet so far -- for one person (John Doe). The YTD cells would be where the running total formula would be saved.

I'm open to setting up the sheet differently, if that would help, but it needs to be easy for supervisors to understand and add the points each day.

Thanks in advance for any help!

Lori Flanigan

Mike TV

@Lori Flanigan

Let me know if you require it to be in the same sheet instead of a different sheet and if you're having problems accomplishing that.

Basically, to convert the formula to being within the same sheet you'd made small changes to the formula. Such as {Sunday Points} would change to Sunday:Sunday, {Sunday Date} would change to [Sunday Date]:[Sunday Date].

Mike TV

@Lori Flanigan

Hi Lori. Can you define 6 months more please? Are months only going to start on the 1st or could someone jump in there say on the 15th of a month and so their 6 month span would be Jan 15th through June 15th? If someone's tracking could start on the 15th of any given month, are we saying Jan 15th through Feb 15th is a month or is 30 days a month?

Lori Flanigan

Hi Mike,

The six months need to be updated daily. For example, if John Doe accrued 0.25 points on 1/4, those points are not counted in the total as of 7/5.

I hope that helps!

Lori

Mike TV

@Lori Flanigan

Hi Lori. I think I figured it out. Hold tight. I'm finishing writing and testing it.

Mike TV

@Lori Flanigan

Hi Lori. This looks like it works to me.

Example sheet:

You're going to want to add a helper column for each of the days of the week.

Sunday Date column formula:

=Date@row

Monday Date column formula:

=Date@row + 1

And so on adding a +2 for Tuesday, +3 for Wednesday, etc. This identifies the date each point scoring was for. They can be hidden after all of this is set up.

Then I created a separate sheet where you can simply build your list of employee names that reference the master sheet of points. Example:

This sheet has the following Sheet Summary cells:

6 months ago formula (make sure to format this sheet summary cell as a date):

=DATE([Today Year]#, [Today Month]# - 6, [Today Day]#)

Today Day formula:

=DAY(TODAY())

Today Month formula:

=MONTH(TODAY())

Today Year formula:

=YEAR(TODAY())

Then for the magic...Points Last 6 months column formula:

=SUMIFS({Sunday Points}, {Name}, =Name@row, {Sunday Date}, >=[6 months ago]#, {Sunday Date}, <=TODAY()) + SUMIFS({Monday Points}, {Name}, =Name@row, {Monday Date}, >=[6 months ago]#, {Monday Date}, <=TODAY()) + SUMIFS({Tuesday Points}, {Name}, =Name@row, {Tuesday Date}, >=[6 months ago]#, {Tuesday Date}, <=TODAY()) + SUMIFS({Wednesday Points}, {Name}, =Name@row, {Wednesday Date}, >=[6 months ago]#, {Wednesday Date}, <=TODAY()) + SUMIFS({Thursday Points}, {Name}, =Name@row, {Thursday Date}, >=[6 months ago]#, {Thursday Date}, <=TODAY()) + SUMIFS({Friday Points}, {Name}, =Name@row, {Friday Date}, >=[6 months ago]#, {Friday Date}, <=TODAY()) + SUMIFS({Saturday Points}, {Name}, =Name@row, {Saturday Date}, >=[6 months ago]#, {Saturday Date}, <=TODAY())

Mike TV

@Lori Flanigan

Let me know if you require it to be in the same sheet instead of a different sheet and if you're having problems accomplishing that.

Basically, to convert the formula to being within the same sheet you'd made small changes to the formula. Such as {Sunday Points} would change to Sunday:Sunday, {Sunday Date} would change to [Sunday Date]:[Sunday Date].

Lori Flanigan

@Mike TV, thank you so much! I have everything working on one sheet and I have a second sheet working, just in case. The team asking for this was hoping for a multi dropdown column for the individual points (0.25, 0.50), but that isn't an option that works.

I will build this out a bit and introduce it next week.

You are amazing at formulas and figuring this one out! Thanks again!

Lori Flanigan