Sum with time elapsed criteria

[atom29]

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!

[DonkeyOte]

I suspect you could do this with a clever FREQUENCY array but it's too early for that for me I'm afraid...

An alternative would be to use a single column of additional calcs adjacent to your source data which should simplify result matrix calc.

Using your sample...

D2:

=1+SUMIF($B$1:$B1,$B2,$C$1:$C1)*(DATEDIF(IFERROR(LOOKUP(2,1/($B$1:$B1=$B2),$A$1:$A1),$A2),$A2,"m")<=6)

copied down to D17



F7:

=LOOKUP(2,1/($B$2:$B17=$E7),$D$2:$D$17)

copied down to F11

the above would generate results of 1,1,1,6 and 3 (I believe last should be 3 and not 2 as per your example).

I have avoided use of Arrays but it should be noted that the LOOKUP approach outlined is not hugely efficient when used in large volume and / or with large precedent ranges.

Were you to sort your source data by Ee and then date the calculation would be simplified.

[atom29]

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.

[DonkeyOte]

I cannot seem to link to a specific post within a thread but I generally refer people to:

Post #5: http://www.excelforum.com/excel-form...ifference.html

Post #14: http://www.excelforum.com/excel-gene...html?p=2100732

The former is summarised and the latter goes into a little more detail using a worked example - although the context behind the question may be different to yours the underlying premise of the approach remains the same.

[atom29]

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.

[DonkeyOte]

No need to apologise - oversight on my part I think as this requirement is rather self evident!

You could perhaps try

D2:

=IF($C2>0,SUM($C2,IFERROR(LOOKUP(2,1/($B$1:$B1=$B2),$D$1:$D1*($A$1:$A1>EDATE($A2,-6))),0)),"")

copied down

[atom29]

This worked great thanks for all the help!