Forum Discussion

Stefan Meier's avatar
Stefan Meier
Copper Contributor
Sep 27, 2017

How can I calculated the maximum of the values in a column based on conditions on other columns

This is an Excel 2010 problem. I know that in later Excel versions there is a MAXWENN function and a MAXWENNS function but I cannot use those because in this company there is only Excel 2010.   I h...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 27, 2017

Hi Stefan,

 

Do you use your formula as array one (Ctrl+Shift+Enter)?

  • Stefan Meier's avatar
    Stefan Meier
    Copper Contributor
    Sep 27, 2017
    Hi Sergei,

    I tried that too.. but it did not change the result.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 27, 2017

      Stefan,

       

      I'm in English interface, bit hard to sort out your formula. Instaed i reproduce small table, hope close to your logic

      Fomula is (array one)

      =MAX(
   IF(
      ([@Nr]=Table1[[#All],[Ref]])*(Table1[[#All],[Status]]<>"done"),
      Table1[[#All],[Res '[Tage']]]
   )
)

      Try to open attached file, it shall be shown in your local interface.

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 27, 2017

        In addition - AND doesn't work in array formulas. Instead of AND(range1, range2) it shall be

        range1*range2

Resources