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

Posted on

QUESTION :

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

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!