Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
Do you know much about VBa? –  Dave Rook Nov 23 '12 at 9:18
add comment

1 Answer 1

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.

enter image description here

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

share|improve this answer
    
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
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.