Max value that meets a criteria
 

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

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

"David Burr" wrote in message
...
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 wrote:
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.

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-----
Bernard Liengme wrote:
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.
.

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.

wrote:
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-----
Bernard Liengme wrote:

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

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:F 123)

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

Any ideas?

Thank you.

Dimitrios


--
dimtsan
------------------------------------------------------------------------
dimtsan's Profile: http://www.excelforum.com/member.php...o&userid=19998
View this thread: http://www.excelforum.com/showthread...hreadid=332863

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!


--
dimtsan
------------------------------------------------------------------------
dimtsan's Profile: http://www.excelforum.com/member.php...o&userid=19998
View this thread: http://www.excelforum.com/showthread...hreadid=332863