Finding MAX date with an Array Formula in Excel

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

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

If a cell in criteria_range1 meets that condition, it returns TRUE. If a cell in criteria_range2 meets that condition it too returns TRUE. Two TRUEs in the same row, which is AND, multiplied together give 1, any combination of FALSE/FALSE, FALSE/TRUE, TRUE/FALSE gives 0. Any non-zero amount is treated as True by Excel, 0 as False, so date_range evaluates to either its value or the non-true condition (False here as we don't have one). So you get a total array of value in date_range where all conditions are met, and False values, allowing MAX to do its bit on. As you can imagine, this works no matter how many conditions there are.

My Notes:
The formula must be committed with CTRL SHIFT ENTER which puts the curly braces {} around the formula.
The criteria ranges must all be the same shape (in this case 1 column wide by the same number of rows deep. I also used dynamic range names.)
The "criteria" can be text or values entered in the formula inside "", or can be a cell reference to the criteria.