I went back and tested and found that Mike's works, AS IS.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I'm not sure I understand your response. The array formula Mike posted
produces correct results under the two conditions Harlan posted (all
negative values and/or text in Column B) where as Shane's non-array
formula fails. Since Shane offered his formula as an alternative to
Mike's, all I think Harlan was doing was pointing out that Mike's array
formula was superior to Shane's non-array alternative because it didn't
fail under the those two conditions.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Then why didn't he post this CSE?

=MAX(IF(($A$1:$A$16=$D$1)*($B$1:$B$160),$B$1:$B$1 6))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I think Harlan was referring to Mike's posted array formula as the
solution.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Harlan, When you are being semipicky, is it not incumbent on you to
provide your solution?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harlan Grove" wrote in message
...
Shane Devenshire wrote...
Here is a non-array approach:

=SUMPRODUCT(MAX((A1:A6=H1)*B1:B6))
...

Semipicky: this fails if there are negative values in col B, in which
case the largest value could be negative; also fails if any cell in
col B is nonnumeric text, in which case this formula would return
#VALUE!.

There are times when array formulas ARE the most robust of various
alternatives. This is one of those times.