How to count the most recent value based on multiple criteria?
- by Andrew
I keep a log of phone calls like the following where the F column is LVM = Left Voice Mail, U = Unsuccessful, S = Successful.
A1 1 B1 Smith C1 John D1 11/21/2012 E1 8:00 AM F1 LVM
A2 2 B2 Smith C2 John D2 11/22/2012 E1 8:15 AM F2 U
A3 3 B3 Harvey C3 Luke D3 11/22/2012 E1 8:30 AM F3 S
A4 4 B4 Smith C4 John D4 11/22/2012 E1 9:00 AM F4 S
A5 5 B5 Smith C5 John D5 11/23/2012 E5 8:00 AM F5 LVM
This is a small sample. I actually have over 700 entries. In my line of work, it is important to know how many unsuccessful (LVM or U) calls I have made since the last Successful one (S). Since values in the F column can repeat, I need to take into consideration both the B and C column.
Also, since I can make a successful call with a client and then be trying to contact them again, I need to be able to count from the last successful call. My G column is completely open which is where I would like to put a running total for each client (G5 would = 1 ideally while G4 = 0, G3 = 0, G2 = 2, G1 = 1 but I want these values calculated automatically so that I do not have scroll through 700 names).
