SUMPRODUCT to return date of last payment?

[wx4usa]

Can I use SUMPRODUCT to return the date and amount of last payment if
I provide the account number?

Column A is Date mm/dd/yyyy
Column B is account number (numeric 6 digits)
Column C is type: PMNT, Purchase
Column D is amount, either a + or - based on Mayment or Purchase

 

[JMay]

Index() is better


Try:

=INDEX(MAX(A2:A10),(B2:B10=G11)) Commit with Ctrl+Shift+Enter (not just
enter)

{ } should appear on each side of the formula - these can not be keyboard
entered.

where G11 is your acct Number
A:A Dates; B:B Acct Numbers

 

[wx4usa]

Index() is better

Try:

=INDEX(MAX(A2:A10),(B2:B10=G11))  Commit with Ctrl+Shift+Enter (notjust
enter)

{    } should appear on each side of the formula - these can not be keyboard
entered.

where G11 is your acct Number
A:A Dates; B:B Acct Numbers

I cannot get it to return the dollar amount of teh last pay

 

[Dave Peterson]

If x999 contains the account number, then you can use this to get the date:
=LOOKUP(2,1/(b1:b100=x999),a1:a100)

Change A1:a100 to d1:d100 for the payment/purchase price.

If you have to look for pmnt in column C at the same time:

=LOOKUP(2,1/((b1:b100=x999)*(c1:c100="Pmnt")),a1:a100)


Both of these are array formulas.

Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap
curly brackets {} around your formula. (don't type them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

 

[wx4usa]

If x999 contains theaccountnumber, then you can use this to get the date:
=LOOKUP(2,1/(b1:b100=x999),a1:a100)

Change A1:a100 to d1:d100 for the payment/purchase price.

If you have to look for pmnt in column C at the same time:

=LOOKUP(2,1/((b1:b100=x999)*(c1:c100="Pmnt")),a1:a100)

Both of these are array formulas.

Hit ctrl-shift-enter instead of enter.  If you do it correctly, excel will wrap
curly brackets {} around your formula.  (don't type them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

wx4usawrote:

Dave,

I am still having trouble getting this to work.

Column A is date, B is Account number, C is type (chg or pmnt), D is
amount paid (+or-)

In one cell I have account number to look up. One cell returns the
date of last payment (pmnt) One cell contains the amount of last
payment.

Any help you could provide would be helpful.

 

[Dave Peterson]

What formula did you use?

If you put the values to match in other cells, what were the values and what
were the addresses of the cells?

If you're matching on numbers, did you enter a numeric value?
If you're matching on digits that are text, did you enter the value the same way
(as text)?

Did you remember to hit ctrl-shift-enter?

 

[wx4usa]

What formula did you use?

If you put the values to match in other cells, what were the values and what
were the addresses of the cells?

If you're matching on numbers, did you enter a numeric value?  
If you're matching on digits that are text, did you enter the value the same way
(as text)?

Did you remember to hit ctrl-shift-enter?



wx4usawrote:

I did get it to return the amount that corresponds to a payment on an
account.

But now how do I add the last payment criteria which would be the max
date + account column + pmnt column and return the date and amount of
that payment.

I may have 20 or 30 payments on each of the accounts and I need the
date and amount of the last one. Does that make sense? Thanks Dave

 

[Dave Peterson]

Just keep adding to the multiplication terms in this expression:

=LOOKUP(2,1/((b1:b100=x999)*(c1:c100="Pmnt")),a1:a100)