Sumif criteria appears twice

[vadda]

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 Sjoblom]

Where are the dates?

--


Regards,


Peo Sjoblom

 

[vadda]

The dates are in column M1:M20

Where are the dates?

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

=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]

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]

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]

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

 

[ShaneDevenshire]

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(MAX((B1:B20="x")*(M1:M20)),M1:M20,0)))

 

[Peo Sjoblom]

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

--


Regards,


Peo Sjoblom

--


Regards,


Peo Sjoblom

 

[ShaneDevenshire]

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