Last week I was struggling with getting an array formula to work properly with the MAX function.
I had a column of business units, a column of dates and a status column.
I wanted to find the most recent date for any given business unit where the status was a particular criteria.
I did try using Bob Phillip's sum product page and Chris Pearson's array formulas page, but it still wasn't working right. Fortunately Bob emailed me with the answer and explained it very well. (I was missing the IF function).
Heres the formula and what Bob said about it:
=MAX(IF((criteria_range1="criteria1")* (criteria_range2="criteria2")* (criteria_range3="criteria3"), date_range))