Formula to Find Latest Transaction Date and amount for a customer

P

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
 
F

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
 
R

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

Phil B

Ragdyer said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top