Forum Discussion

chahine's avatar
chahine
Iron Contributor
Jul 28, 2021

MAXIFS

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, 

 

excel

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 28, 2021

    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.

    • chahine's avatar
      chahine
      Iron Contributor
      Jul 28, 2021
      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

Resources