excel Max IF function

Question

I was reading the following post which really helped:

"I am trying to find the maximum value based on another column. This is what I have tried:

=MAX(IF(Sheet3!$B$2:$B$5491=Sheet4!A3,Sheet3!$E$2:$E$5491))

How I am reading this code is that if the criteria in sheet 3 in column B is equivalent to the cell in A3 in sheet 4, then return the maximum value that would be in column E from sheet 3. I found this code online and have tried variations but all it is doing is returning 0. Is there another way I could go about finding the max value?"

However, the two columns I am comparing are dates. I need to find the MAX of one column if the Date in Sheet1!A3 is before or equal to the date in Sheet3B2:B5491.

Answer 1

You are looking at an array formula. To make it work you have to press CTRL+SHIFT+ENTER instead of ENTER. That will add braces in the formula and convert it to an array formula.

To find the MAX of one column if the Date in Sheet1!A3 is before or equal to the date in Sheet3B2:B5491 should be the following:

=MAX(IF(Sheet3!$B$2:$B$5491<=Sheet1!A3,Sheet3!$E$2:$E$5491))

Remember to press CTRL+SHIFT+ENTER instead of ENTER, it will look like that when done:

{=MAX(IF(Sheet3!$B$2:$B$5491<=Sheet1!A3,Sheet3!$E$2:$E$5491))}