MAXIFS

[chahine]

Hello, wanted to ask, why am getting the error ,i want to find max profit for certain criteria, when i put in the maxrange in red of the formula MAXIFS(O5:O18-P5:P18,N5:N18,R8:R9), why its giving this error, although o5:o18-p5:p18 should do the subtraction perfectly (i tried pressing F9 and its doing subtraction)

any suggestion why am getting the error, i just want to understand this small tip, 

[#2]

@chahine 

The first argument of MAXIFS must be a range, it cannot be an expression.

Workaround:

Create a helper column with formula =O5:O18-P5:P18, and use the helper column as first argument in your MAXIFS formula.

But I don't understand why you use two cells R8:R9 as criteria. Excel will use R8 and ignore R9.

[#3]

Hi Hans, Thanks, i thought i can use expression, as i didnt want to make helper column

i did the criteria as R8:R9 as MAXIFS supports arrays, so it will spill the 2 answers directly