How would one set up a formula for:
Range B1:B20 is text and is sometimes duplicated, range A1:A20 has amounts.
If duplicated I need the amount of the later dated one to be the formula
result.

Thanks for your time.

Where are the dates?

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
How would one set up a formula for:
Range B1:B20 is text and is sometimes duplicated, range A1:A20 has
amounts.
If duplicated I need the amount of the later dated one to be the formula
result.

Thanks for your time.

The dates are in column M1:M20

"Peo Sjoblom" wrote:

Where are the dates?

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
How would one set up a formula for:
Range B1:B20 is text and is sometimes duplicated, range A1:A20 has
amounts.
If duplicated I need the amount of the later dated one to be the formula
result.

Thanks for your time.

=MAX(IF((B1:B20="x")*(M1:M20),A1:A20))


entered with ctrl + shift & enter



will return what's in A1:A20 where B1:B20 is x (replace x with your
criteria)

and if there are more than one occurrence of x it will return the one with
the
most recent/later date in M1:M20

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
The dates are in column M1:M20

"Peo Sjoblom" wrote:

Where are the dates?

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
How would one set up a formula for:
Range B1:B20 is text and is sometimes duplicated, range A1:A20 has
amounts.
If duplicated I need the amount of the later dated one to be the
formula
result.

Thanks for your time.

Peo,

Thank you for the suggestion, but the formula below returns a value even if
the text occurs once. I am looking for a formula that will only return a
value if the text appears twice. Maybe I'm doing something wrong.

"Peo Sjoblom" wrote:


=MAX(IF((B1:B20="x")*(M1:M20),A1:A20))


entered with ctrl + shift & enter



will return what's in A1:A20 where B1:B20 is x (replace x with your
criteria)

and if there are more than one occurrence of x it will return the one with
the
most recent/later date in M1:M20

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
The dates are in column M1:M20

"Peo Sjoblom" wrote:

Where are the dates?

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
How would one set up a formula for:
Range B1:B20 is text and is sometimes duplicated, range A1:A20 has
amounts.
If duplicated I need the amount of the later dated one to be the
formula
result.

Thanks for your time.

You didn't say that in your original post

"If duplicated I need the amount of the later dated one to be the formula
result."

You didn't say that you didn't want any result at all if it only occurred
once


=IF(COUNTIF(B1:B20,"x")<2,"",MAX(IF((B1:B20="x")*( M1:M20),A1:A20)))

entered the same way will return blank if it occurs once




--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
Peo,

Thank you for the suggestion, but the formula below returns a value even
if
the text occurs once. I am looking for a formula that will only return a
value if the text appears twice. Maybe I'm doing something wrong.

"Peo Sjoblom" wrote:


=MAX(IF((B1:B20="x")*(M1:M20),A1:A20))


entered with ctrl + shift & enter



will return what's in A1:A20 where B1:B20 is x (replace x with your
criteria)

and if there are more than one occurrence of x it will return the one
with
the
most recent/later date in M1:M20

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
The dates are in column M1:M20

"Peo Sjoblom" wrote:

Where are the dates?

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
How would one set up a formula for:
Range B1:B20 is text and is sometimes duplicated, range A1:A20 has
amounts.
If duplicated I need the amount of the later dated one to be the
formula
result.

Thanks for your time.

Thanks for your help Peo, this did find the final result. I really appreciate
you time.

"Peo Sjoblom" wrote:

You didn't say that in your original post

"If duplicated I need the amount of the later dated one to be the formula
result."

You didn't say that you didn't want any result at all if it only occurred
once


=IF(COUNTIF(B1:B20,"x")<2,"",MAX(IF((B1:B20="x")*( M1:M20),A1:A20)))

entered the same way will return blank if it occurs once




--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
Peo,

Thank you for the suggestion, but the formula below returns a value even
if
the text occurs once. I am looking for a formula that will only return a
value if the text appears twice. Maybe I'm doing something wrong.

"Peo Sjoblom" wrote:


=MAX(IF((B1:B20="x")*(M1:M20),A1:A20))


entered with ctrl + shift & enter



will return what's in A1:A20 where B1:B20 is x (replace x with your
criteria)

and if there are more than one occurrence of x it will return the one
with
the
most recent/later date in M1:M20

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
The dates are in column M1:M20

"Peo Sjoblom" wrote:

Where are the dates?

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
How would one set up a formula for:
Range B1:B20 is text and is sometimes duplicated, range A1:A20 has
amounts.
If duplicated I need the amount of the later dated one to be the
formula
result.

Thanks for your time.

Hi,

I probably don't understand something on this one, but why don't you test
the previous formula by reversing the order of the two dates and see if it
still works. In other words if M5 has the earlier date and M15 the later
date, change the dates in M15 and M5 and see if the formula still returns the
correct value. If I read the formula correctly it is returning the Max value
in column A not the value for the Max date in column M.

If this is so, here is one solution:

=IF(COUNTIF(B1:B20,"x")<2,"",INDEX(A1:A20,MATCH(MA X((B1:B20="x")*(M1:M20)),M1:M20,0)))

--
Thanks,
Shane Devenshire


"vadda" wrote:

Thanks for your help Peo, this did find the final result. I really appreciate
you time.

"Peo Sjoblom" wrote:

You didn't say that in your original post

"If duplicated I need the amount of the later dated one to be the formula
result."

You didn't say that you didn't want any result at all if it only occurred
once


=IF(COUNTIF(B1:B20,"x")<2,"",MAX(IF((B1:B20="x")*( M1:M20),A1:A20)))

entered the same way will return blank if it occurs once




--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
Peo,

Thank you for the suggestion, but the formula below returns a value even
if
the text occurs once. I am looking for a formula that will only return a
value if the text appears twice. Maybe I'm doing something wrong.

"Peo Sjoblom" wrote:


=MAX(IF((B1:B20="x")*(M1:M20),A1:A20))


entered with ctrl + shift & enter



will return what's in A1:A20 where B1:B20 is x (replace x with your
criteria)

and if there are more than one occurrence of x it will return the one
with
the
most recent/later date in M1:M20

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
The dates are in column M1:M20

"Peo Sjoblom" wrote:

Where are the dates?

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
How would one set up a formula for:
Range B1:B20 is text and is sometimes duplicated, range A1:A20 has
amounts.
If duplicated I need the amount of the later dated one to be the
formula
result.

Thanks for your time.

You are right Shane, it was sloppy of me not testing the
values better.

--


Regards,


Peo Sjoblom

--


Regards,


Peo Sjoblom

"ShaneDevenshire" wrote in
message ...
Hi,

I probably don't understand something on this one, but why don't you test
the previous formula by reversing the order of the two dates and see if it
still works. In other words if M5 has the earlier date and M15 the later
date, change the dates in M15 and M5 and see if the formula still returns
the
correct value. If I read the formula correctly it is returning the Max
value
in column A not the value for the Max date in column M.

If this is so, here is one solution:

=IF(COUNTIF(B1:B20,"x")<2,"",INDEX(A1:A20,MATCH(MA X((B1:B20="x")*(M1:M20)),M1:M20,0)))

--
Thanks,
Shane Devenshire


"vadda" wrote:

Thanks for your help Peo, this did find the final result. I really
appreciate
you time.

"Peo Sjoblom" wrote:

You didn't say that in your original post

"If duplicated I need the amount of the later dated one to be the
formula
result."

You didn't say that you didn't want any result at all if it only
occurred
once


=IF(COUNTIF(B1:B20,"x")<2,"",MAX(IF((B1:B20="x")*( M1:M20),A1:A20)))

entered the same way will return blank if it occurs once




--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
Peo,

Thank you for the suggestion, but the formula below returns a value
even
if
the text occurs once. I am looking for a formula that will only
return a
value if the text appears twice. Maybe I'm doing something wrong.

"Peo Sjoblom" wrote:


=MAX(IF((B1:B20="x")*(M1:M20),A1:A20))


entered with ctrl + shift & enter



will return what's in A1:A20 where B1:B20 is x (replace x with your
criteria)

and if there are more than one occurrence of x it will return the
one
with
the
most recent/later date in M1:M20

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
The dates are in column M1:M20

"Peo Sjoblom" wrote:

Where are the dates?

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
How would one set up a formula for:
Range B1:B20 is text and is sometimes duplicated, range A1:A20
has
amounts.
If duplicated I need the amount of the later dated one to be
the
formula
result.

Thanks for your time.

Hi,

If in fact I was correct, here is a simplier solution:

=LOOKUP(MAX((B1:B20="x")*(M1:M20)),M1:M20,A1:A20)

this is array entered: Shift+Ctrl+Enter
--
Thanks,
Shane Devenshire


"Peo Sjoblom" wrote:

You are right Shane, it was sloppy of me not testing the
values better.

--


Regards,


Peo Sjoblom

--


Regards,


Peo Sjoblom

"ShaneDevenshire" wrote in
message ...
Hi,

I probably don't understand something on this one, but why don't you test
the previous formula by reversing the order of the two dates and see if it
still works. In other words if M5 has the earlier date and M15 the later
date, change the dates in M15 and M5 and see if the formula still returns
the
correct value. If I read the formula correctly it is returning the Max
value
in column A not the value for the Max date in column M.

If this is so, here is one solution:

=IF(COUNTIF(B1:B20,"x")<2,"",INDEX(A1:A20,MATCH(MA X((B1:B20="x")*(M1:M20)),M1:M20,0)))

--
Thanks,
Shane Devenshire


"vadda" wrote:

Thanks for your help Peo, this did find the final result. I really
appreciate
you time.

"Peo Sjoblom" wrote:

You didn't say that in your original post

"If duplicated I need the amount of the later dated one to be the
formula
result."

You didn't say that you didn't want any result at all if it only
occurred
once


=IF(COUNTIF(B1:B20,"x")<2,"",MAX(IF((B1:B20="x")*( M1:M20),A1:A20)))

entered the same way will return blank if it occurs once




--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
Peo,

Thank you for the suggestion, but the formula below returns a value
even
if
the text occurs once. I am looking for a formula that will only
return a
value if the text appears twice. Maybe I'm doing something wrong.

"Peo Sjoblom" wrote:


=MAX(IF((B1:B20="x")*(M1:M20),A1:A20))


entered with ctrl + shift & enter



will return what's in A1:A20 where B1:B20 is x (replace x with your
criteria)

and if there are more than one occurrence of x it will return the
one
with
the
most recent/later date in M1:M20

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
The dates are in column M1:M20

"Peo Sjoblom" wrote:

Where are the dates?

--


Regards,


Peo Sjoblom

"vadda" wrote in message
...
How would one set up a formula for:
Range B1:B20 is text and is sometimes duplicated, range A1:A20
has
amounts.
If duplicated I need the amount of the later dated one to be
the
formula
result.

Thanks for your time.