Excel: Using MAXIFS with OR function-CodePudding

I'm am hoping to use an OR function in a MAXIFS function as one of the criteria, but it doesn't seem to work.

=MAXIFS(P:P,O:O,OR("<67",362))

Basically, I just want to find the max of a value in one range, if the value in an adjacent range is either less than 67 or equal to 362. I know the problem is with how I am trying to use the OR function, because if I just use "<67" then it works, but I want to 362 to be part of that range as well.

CodePudding user response：

You could run 2 MAXIFS and find the max between them:

=MAX(MAXIFS(P:P, O:O, "<67"), MAXIFS(P:P, O:O, 100))

CodePudding user response：

Use the following formula:

=MAX(IF((O:O<67),P:P),IF((O:O=361),P:P))

This formula uses the IF function twice, one for each criteria specified.

The first IF function filters the values from range P:P where the corresponding values in range O:O are less than 67, and the second IF function filters the values from range P:P where the corresponding values in range O:O are equal to 361.

The MAX function then only considers the filtered values and returns the maximum value that meets the criteria.