Employee Attendance Tracker- Rolling 90 Days

Hello

I am an Excel Novice and not good with formulas so I am hoping someone can help me. I am trying to create an attendance tracker that will sum up points for the current to previous 90 days. I put in a drop down of the offense and want it to populate the correct value, and then sum all offenses for the last 90 days in the rolling points cell. I hoping someone has done this before and its an easy fix. Thanks for any help you can provide.

Awesome thanks so much!

And to get the points at the top, you can enter this formula in cell D2:

=COUNTIFS(D$12:D$20,$A2)*XLOOKUP($A2,Sheet1!$B$6:$B$10,Sheet1!$C$6:$C$10,"Not found",0)

and then copy it down and across as far as you need

These work great thank you! The only issue now is there a way to get it to tally by employee? So if I sort it will tally the points for that employee's last 90 days?

Updated file attached

The Tally column would be in A9

So in B1 you could enter this formula:

=SUM(XLOOKUP(FILTER($D12:$SD12,$D$1:$SD$1>=TODAY()-90),Sheet1!$B$6:$B$10,Sheet1!$C$6:$C$10,0,0))

and then copy it down by each employee.

It looks to still be tallying by date instead of by employee. I would need the result to be points accrued in the last 90 days by employee in cell A9.

I moved the formula to B12. But unless I am doing something wrong the tally in A9 is counting the points by date. So more than 1 employee can accrue points in a day. But the tally should only show 1 employee at a time and calculate their points over 90 days. When I sort to 1 employee the numbers don't change. The tally is still counting the points accrued on that day in total.

I just saw the attachment. I think that did it! Thank you very much!!!

Hello Again. How do I get this new version to copy down to all employees? It only seems to be calculating for Employee 1.

I gave this solution before, but it seems to work for what you are asking. It calculates BOTH for each individual employee and also calculates for all (for all is in cell A9, and each employee is in range B12:B20). It uses features in O365.

I changed the date range to start with May 1 to show it does not include data from before 90 days. Each date in row 1 is incremented 1 day from the previous so you can change the date in cell D1 to see how it changes depending on if it's prior to 90 days or not. The formulas in the top range and for each employee are alternatives to what you've already been given.

Hi Gregb11. Last night bebo021999 sent me an alternate solution which is what I am asking about. The solution you sent still is not calculating properly and doesn't calculate when the employees are sorted. If you look at your example, Employee 9 should have 12 points but is returning 0 points. Employee 7 should have 1 point and is showing 0. I am looking to build on the example that bebo021999 sent me last night. I am grateful for your input.

Employee 9 should not have any points because the date if the No Call No Show is more than 90 days before today. If you change the date in cell D1 to May 10, say, you will see the points change because now they would be within 90 days. Isn't that your requirement? (Same thing for Employee 7).

I don't know what you mean by when the employees are sorted. If you just sort the employees, then all the attendance tracking is off. OR, when you sort the employees, are you also sorting all the call outs, tardy, etc. as well? If you are, I would put the whole thing into an Excel table. Then everything sorts correctly.

Anyway, there are many solutions to a problem and bebo21999 is an expert for sure so you can't go wrong with that. Good luck.

Ah, I didn't see that requirement to get the top range based on the employee that is filtered. Good job on that.

Hello bebo021999, I am not sure if you saw my last post but I have been playin with this and it looks like the issue is the proper values assigned to the dropdown are not returning for A5 and A6. A5 is returning a value of 2 and should be .5. A6 is returning ga value of 2 or 0 and it should be returning 12. Also it looks like nothing is being returned for employees below Employee 2. I have included examples that are selected in the file to show. Can you help with this?

FANTASTIC!! Works Great!