Search code examples
excelsumifs

Finding past occurrence

I have a set of stock units transacted (ordered by date from old to new).

I want to find the Previous Quantity that was transacted.

Here is a sample dataset:

 Date(A) Type(B) Stock(C) Qty(D) PrevQty(E) CumulativeQty(F) 
 2016-01-03 Buy MSFT 100 0 100
 2016-01-04 Buy GOOG 500 0 500
 2016-01-05 Buy MSFT 100 100 200
 2016-01-06 Sell MSFT 100 100 100

I can compute CumulativeQty via: 

=SUMIFS(D:D,C:C, C4,A:A, "<=" &A4)

Question: How do I figure out PrevQty?

Solution

  • This formula is designed for your cell E3.

    =IFERROR(LOOKUP(2,1/($C$2:$C2=$C3),$D$2:$D2),0)

    Basically, it looks for the last occurrence of the 'Stock' in C3. However, that last occurrence would naturally by in C3 itself. Therefore the row in which the formula resides is excluded from the range in which the last occurrence is sought.

    The presumption is that your first data are in row 2 in your worksheet.