Formula to Find Latest Transaction Date and amount for a customer
 

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

Formula to Find Latest Transaction Date and amount for a customer
 

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

--
Regards,
Fred


"Phil B" wrote in message
...
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

Formula to Find Latest Transaction Date and amount for a customer
 

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Phil B" wrote in message
...
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

Formula to Find Latest Transaction Date and amount for a customer
 

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

Formula to Find Latest Transaction Date and amount for a customer
 

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


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Phil B" wrote in message
...
Ragdyer wrote:
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

Formula to Find Latest Transaction Date and amount for a customer
 

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

Formula to Find Latest Transaction Date and amount for a customer
 

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phil B" wrote in message
...
Ragdyer wrote:
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