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