Sum with time elapsed criteria

Hello all-

I am usually pretty creative at solving my own excel problems. I'm sure I don't utilize best practices, but I can usually get the result that I need. I have been scratching my brain on this one all day, so I figured it was time to seek some help. Somewhat complex to exlpain exactly what I need, so I'll do my best.

I have three columns of data....date, employee name and points. Employees receive points for certain things, and those points accumulate over time. However, if after 6 months an employee has not received a point.....they start back over at 0.

I have been having difficulty coming up with a way to identify how many cumulative points an employee has. I thought I was making it too difficult (and I still likely am), so I had a lightbulb moment and used the formula in the worksheet attached. Then, my lightbulb shattered when I realized it didn't do what I needed.

For example, an employee could go 5 months between points over and over again, and still continue to accumulate more and more points. Is there a way anyone can think of to compare the point dates for each employee, look for dates seperated by 6 months, and then return the max of those dates? Then I could use that to sum all points where the point date was greater than this day???

Or an easier solution?

Thanks so much for any help or suggestions!

Thank you so much for the reply.....especially at the time that you posted! This works! Although I don't quite understand why.....

Would you mind explaining what is going on in the formulas? Especially the 1/($B$2:$B17=$E7) in the lookup.

OK....so I thought it worked.

I added a couple of rows of data to test a couple things, and it produced some undesirable results.

It seems it will reset back to zero after the first point is achieved and 6 months have passed, however, on the next received point it adds all points up regardless of date. Also, it seems to have an issue with using half of a point? I apologize for throwing that in now, as that wasn't part of the original dataset.

I also slightly modified the formula from DonkeyOte to meet my criteria of less than 6 months, and also added an IF to place a blank if no data is present.

I sincerely appreciate any additional ideas/help. Thanks.

This worked great thanks for all the help!