CODEWITHSUNDEEP
excelexcel-formula
CoryDog
CoryDog

Reputation: 23

Excel: Using MAXIFS with OR function

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.

Upvotes: 2

Views: 4074

Answers (3)

Jos Woolley
Jos Woolley

Reputation: 9052

Similar to Max L's reply, though a touch more succinct:

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

Upvotes: 2

Mertinc
Mertinc

Reputation: 781

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.

Upvotes: 1

MaxxL
MaxxL

Reputation: 15

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

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

Upvotes: 1

Related Questions