Sumif criteria appears twice

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vadda - 13 Oct 2008 17:50 GMT

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.

Reply to this Message

Peo Sjoblom - 13 Oct 2008 17:57 GMT

Where are the dates?

Signature

Regards,

Peo Sjoblom

> How would one set up a formula for:
> Range B1:B20 is text and is sometimes duplicated, range A1:A20 has
[quoted text clipped - 3 lines]
>
> Thanks for your time.

Reply to this Message

vadda - 13 Oct 2008 18:06 GMT

The dates are in column M1:M20

> Where are the dates?
>
[quoted text clipped - 5 lines]
> >
> > Thanks for your time.

Reply to this Message

Peo Sjoblom - 13 Oct 2008 18:33 GMT

=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

Signature

Regards,

Peo Sjoblom

> The dates are in column M1:M20
>
[quoted text clipped - 8 lines]
>> >
>> > Thanks for your time.

Reply to this Message

vadda - 13 Oct 2008 18:52 GMT

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.

> =MAX(IF((B1:B20="x")*(M1:M20),A1:A20))
>
[quoted text clipped - 19 lines]
> >> >
> >> > Thanks for your time.

Reply to this Message

Peo Sjoblom - 13 Oct 2008 19:07 GMT

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

Signature

Regards,

Peo Sjoblom

> Peo,
>
[quoted text clipped - 27 lines]
>> >> >
>> >> > Thanks for your time.

Reply to this Message

vadda - 13 Oct 2008 19:30 GMT

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

> You didn't say that in your original post
>
[quoted text clipped - 39 lines]
> >> >> >
> >> >> > Thanks for your time.

Reply to this Message

ShaneDevenshire - 14 Oct 2008 00:14 GMT

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)))

Signature

Thanks,
Shane Devenshire

> Thanks for your help Peo, this did find the final result. I really appreciate
> you time.
[quoted text clipped - 42 lines]
> > >> >> >
> > >> >> > Thanks for your time.

Reply to this Message

Peo Sjoblom - 14 Oct 2008 00:34 GMT

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

Signature

Regards,

Peo Sjoblom

--

Regards,

Peo Sjoblom

> Hi,
>
[quoted text clipped - 65 lines]
>> > >> >> >
>> > >> >> > Thanks for your time.

Reply to this Message

ShaneDevenshire - 14 Oct 2008 03:10 GMT

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

Signature

Thanks,
Shane Devenshire

> You are right Shane, it was sloppy of me not testing the
> values better.
[quoted text clipped - 68 lines]
> >> > >> >> >
> >> > >> >> > Thanks for your time.

Reply to this Message