# How to count the most recent value based on multiple criteria?

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).

-
Do you know much about VBa? – Dave Nov 23 '12 at 9:18

I have taken the liberty of adding a header row and have replaced your numbers in ColumnA (these effectively match the row numbers, so hopefully not required) with `=B2&“ ”&A2` etc (to distinguish individuals who might share Surname or FirstName with others). Then the formula:

``````=SUM(IF(F2<>"S",G1+IF(F2="S",0,MATCH(A2,A2:A\$700,0)),0))
``````

will hopefully suit – though I have not tested it much beyond the data you have provided.

If this works I may come back later and explain how - but no point if it does not!

-
I like it and it seems simple enough. I will work on it and double check! Thank you! – Andrew Nov 23 '12 at 22:39
Update I notice a slight problem. As you have this currently set, all of column G will add the previous value (as long as the current F is S) even if the Name is different. I believe that your placement of G1 must move. I will work on it and update when I know more. – Andrew Nov 23 '12 at 22:46
@Andrew I have not confirmed what you say but agree you are very probably correct - I did not check 'corner' cases, only that it worked on the small sample. I intend to see if I can 'fix it' but meanwhile (i) my concatenation references are wrong and (ii) here SUM() is wholly redundant. Apologies x 3. – pnuts Nov 23 '12 at 23:04
Number Last First Date Time Result Calls since 694. As Ha 11/21/2012 7:35 AM S 0 695. Gi Ni 11/21/2012 8:32 AM LVM 7 696. Ki Dy 11/21/2012 8:40 AM LVM 6 697. Le Ke 11/21/2012 8:47 AM S 0 698. Li Sp 11/21/2012 9:08 AM LVM 1 699. Or Ha 11/21/2012 9:48 AM LVM 4 700. Li Sp 11/21/2012 10:04 AM S 0 701. He Am 11/21/2012 10:26 AM S 0 702. Th Ca 11/21/2012 10:42 AM S ~ 1 703. Va Ju 11/21/2012 10:52 AM LVM 2 704. Fr No 11/21/2012 12:35 PM S 0 705. Ki Dy 11/21/2012 12:49 PM S 0 706. Ha Jo 11/21/2012 1:16 PM S ~ 2 – Andrew Nov 23 '12 at 23:20
A B C D E F G Number Last First Date Time Result Calls since 694. As Ha 11/21/2012 7:35 AM S 0 695. Gi Ni 11/21/2012 8:32 AM LVM 7 696. Ki Dy 11/21/2012 8:40 AM LVM 6 697. Le Ke 11/21/2012 8:47 AM S 0 698. Li Sp 11/21/2012 9:08 AM LVM 1 699. Or Ha 11/21/2012 9:48 AM LVM 4 700. Li Sp 11/21/2012 10:04 AM S 0 701. He Am 11/21/2012 10:26 AM S 0 702. Th Ca 11/21/2012 10:42 AM S ~ 1 703. Va Ju 11/21/2012 10:52 AM LVM 2 704. Fr No 11/21/2012 12:35 PM S 0 705. Ki Dy 11/21/2012 12:49 PM S 0 – Andrew Nov 23 '12 at 23:30