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

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

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

On Dec 17, 8:47*pm, JMay wrote:
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" wrote:
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

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

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

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

--

Dave Peterson

On Dec 18, 7:38*am, Dave Peterson wrote:
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:

Can I useSUMPRODUCTto return the date and amount of last payment if
I provide theaccountnumber?

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

--

Dave Peterson

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.

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

On Dec 18, 7:38 am, Dave Peterson wrote:
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:

Can I useSUMPRODUCTto return the date and amount of last payment if
I provide theaccountnumber?

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

--

Dave Peterson

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

On Dec 19, 11:24*am, Dave Peterson wrote:
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:

On Dec 18, 7:38 am, Dave Peterson wrote:
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:

Can I useSUMPRODUCTto return the date and amount of last payment if
I provide theaccountnumber?

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

--

Dave Peterson

Dave,

I am still having trouble getting this to work.

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

In one cell I haveaccountnumber 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

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

Just keep adding to the multiplication terms in this expression:

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



wx4usa wrote:

On Dec 19, 11:24 am, Dave Peterson wrote:
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:

On Dec 18, 7:38 am, Dave Peterson wrote:
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:

Can I useSUMPRODUCTto return the date and amount of last payment if
I provide theaccountnumber?

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

--

Dave Peterson

Dave,

I am still having trouble getting this to work.

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

In one cell I haveaccountnumber 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

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