Returning the last item in a subset with excel formula

Question

In this example I would like to mark any customer that has bought a pen most recently(or bottom of the list). I have my data sorted by CustomerID and ServiceDate with the most recent as the last. I would like to be able to mark all of the customer’s transactions only if the last purchase was a pen (333). I have been trying formulas with COUNTA but, not sure how to do it when relying on a subset of data.

=INDEX(C:C,COUNTA(C:C))

This will give me the last value in a column.

Customer ID Custmer Name    Item Number Item Name   Date        Desired Results
1           Bob             222         Paper       1/1/2016    X
1           Bob             111         Tape        1/1/2017    X
1           Bob             333         Pen         1/1/2018    X
4           Greg            333         Pen         1/1/2015    
4           Greg            111         Tape        1/1/2016    
6           Chris           111         Tape        1/1/2015    X
6           Chris           333         Pen         1/1/2018    X
8           Luke            333         Pen         1/1/2013    
8           Luke            333         Pen         1/1/2014    
8           Luke            222         Paper       1/1/2015    
8           Luke            111         Tape        1/1/2016    
8           Luke            111         Tape        1/1/2018    
9           Tom             333         Pen         1/1/2013    X

Answer 1

Thanks to joe I was able to figure this one out.

I still had to make another column.

I put this in column F.

=IF(AND(C2=333,B2<>B3),1,"") 

Then in column G.

=IF(AND(COUNTIFS(A:A,A2,F:F,1)=1),"Yes","")

This worked great.