Max value that meets a criteria

[David Burr]

Imagine I have two columns, e.g. a name and a date. There
can be multiple entries for the same name with different
dates associated with them.

I want to return the maximum date associated with a
particular name.

The easy way to solve this would be with DMAX. However I
don't like the fact that that requires me to store the
search criteria in a separate range as there will be a
number of different criteria for different statistics and
it's going to get very messy. Is there a way to achieve
this with the criteria inline?

I have been playing with sumproduct and max but that
doesn't look like being the answer.

Many thanks


David

 

[Bernard Liengme]

Here are two possible ways
=MAX(--(A1:A10="john")*B1:B10) must be entered as an array formula with
Shift+Ctrl+Enter
and
=SUMPRODUCT(MAX(--(A1:A10="john"),B1:B10))
completed with simple Enter

best wishes
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

 

[Aladin Akyurek]

Here are two possible ways
=MAX(--(A1:A10="john")*B1:B10) must be entered as an array formula with
Shift+Ctrl+Enter
and
=SUMPRODUCT(MAX(--(A1:A10="john"),B1:B10))
completed with simple Enter

best wishes
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

[...]

Not the 2nd formula, please.

 

[Guest]

Thanks very much. I've used the first version and it works
very nicely.

Any chance of you explaining it?

What is the "--" about?

I can see that the first bit requires that the name
matches and we are evaluating column B. What distinguishes
this as an array forumla?

=MAX(--(A$2:A$10=A2)*B$2:B$10)

Many thanks

-----Original Message-----

Here are two possible ways
=MAX(--(A1:A10="john")*B1:B10) must be entered as an array formula with
Shift+Ctrl+Enter
and
=SUMPRODUCT(MAX(--(A1:A10="john"),B1:B10))
completed with simple Enter

best wishes
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

[...]

Not the 2nd formula, please.
.

 

[Aladin Akyurek]

There is in fact no need for coercion by double negation in:

=MAX(--(A1:A10="john")*B1:B10)

for the multiplication operator assumes the coercion duty as a side effect:

=MAX((A1:A10="john")*B1:B10)

which is equivalent to:

=MAX(IF(A1:A10="john"),B1:B10))

All of these formulas must be confirmed with control+shift+enter instead
of just enter.

Thanks very much. I've used the first version and it works
very nicely.

Any chance of you explaining it?

What is the "--" about?

I can see that the first bit requires that the name
matches and we are evaluating column B. What distinguishes
this as an array forumla?

=MAX(--(A$2:A$10=A2)*B$2:B$10)

Many thanks

-----Original Message-----

array formula with

Shift+Ctrl+Enter
and
=SUMPRODUCT(MAX(--(A1:A10="john"),B1:B10))
completed with simple Enter

best wishes
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

[...]

Not the 2nd formula, please.
.

 

[dimtsan]

Hi guys,

I want to use the same formula but with the small difference that the
range refers to different sheet. e.g:
The sheet I am using is called "Targets" and the formula is:
=ΜΙΝ((Projections!C3:C123="test")*Projections!F3:F123)

The above formula is not working eventhough I am pressing Shift +
Control + Enter

Any ideas?

Thank you.

Dimitrios

 

[dimtsan]

Hi guys,

Is the first version working for MIN too? I managed to run the first
solution for max but not for min.


Any ideas?

D!