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.