Formula to Find Latest Transaction Date and amount for a customer

[Phil B]

Have a sheet with Column A showing customer ID and column B showing Date
of transaction and column C showing Transaction amount. I would like to
use a formula to find the most recent transaction date and transaction
amount for a customer Id that is entered into a cell on another sheet.
Thank you for any help.

Phil

 

[Fred Smith]

The Match function will find the row the Client ID is on. The Index function
will access a cell by its row and column number. So,

=index(A:C,match("CustID",A:A,0),2) will find the Date, and
=index(A:C,match("CustID",A:A,0),3) will find the Transaction amount

 

[Ragdyer]

The most *recent* date would be the largest, so, if your data was on Sheet1,
from A1 to C24, try this in Sheet 2, say in B1,with the customer ID entered
in A1:

=SUMPRODUCT(MAX((Sheet1!A1:A24=A1)*Sheet1!B1:B24))

Format Sheet2, B1 as a date.

Now, to return the amount for that date, enter this in C1:

=SUMPRODUCT((Sheet1!A1:A24=A1)*(Sheet1!B1:B24=B1)*Sheet1!C1:C24)

NOTE - if you have *duplicate* recent dates, the formula will *total* the
amounts for those matching duplicates.

 

[Phil B]

The most *recent* date would be the largest, so, if your data was on Sheet1,
from A1 to C24, try this in Sheet 2, say in B1,with the customer ID entered
in A1:

=SUMPRODUCT(MAX((Sheet1!A1:A24=A1)*Sheet1!B1:B24))

Format Sheet2, B1 as a date.

Now, to return the amount for that date, enter this in C1:

=SUMPRODUCT((Sheet1!A1:A24=A1)*(Sheet1!B1:B24=B1)*Sheet1!C1:C24)

NOTE - if you have *duplicate* recent dates, the formula will *total* the
amounts for those matching duplicates.

Thank you very much. I was playing with Sumproduct and max and thought
the solution may be with it but I didn't get there. Both your formulas
worked very well. Do you have any good references on using Sumproduct -
it appears to be an extremely powerful function.

Thanks again
Phil

 

[Ragdyer]

Bob Phillips has a web page on the function which probably is the most
enlightening and informative reference you'll find on the subject:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Phil B]

Bob Phillips has a web page on the function which probably is the most
enlightening and informative reference you'll find on the subject:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Excellent article - Again thank you for your help

Phil

 

[Ragdyer]

You're welcome, and appreciate your feed-back.