Excel Puzzler: MAXIF without an Array Function

Columnist
Share this content
0
3
2387

I recently wrote an article on Creating Excel's Equivalent of MAXIF, in which I used an array formula to find the largest value from a list based on a given criteria. Reader Rob Brebbia read the first paragraph or two of my article, and set out to see if he could find a solution on his own. His research led him to a worksheet function in Excel that he and I had both forgotten about but that does allow you to carry out a "MAXIF" without using an array function. In homage to NPR's Car Talk, we present our first Excel Puzzler. Can you name the worksheet function that Rob found?

Replies

Please login or register to join the discussion.

Please note that your comments on this puzzler thread will be held pending moderator approval until Monday, June 16, so that everyone has a chance to participate. We look forward to your thoughts!

Thanks (0)

SUMPRODUCT(MAX((A2:A4="Joe")*B2:B4))
where
A2:A4= column with independent variables including "Joe"
and
B2:B4= column with dependent values including those for joe

With thanks to V_Agarwal (http://answers.microsoft.com/e... )

Thanks (0)

Ken,

Thanks for participating! The answer that Rob Brebbia proposed (and that I concur with) is DMAX, as documented here: http://office.microsoft.com/en...

David

Thanks (0)