Get Max value with criteria check

09/01/18 Edited 12/09/19

I have Table with values as below and now I would like to get max value. How Could i can achieve the Results Required ?.

Col1    Col2        

A         2580       

A         2380

B        1860

B        420

C        860

D        2760

D        2710

C        2750

Max Value  -- A = 2580

                      B = 1860

                      C = 2750

                      D = 2760

Tags:

Comments

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    This formula:

    =MAX(COLLECT([Col2]:[Col2], [Col1]:[Col1], "A"))

    looks at the complete columns and will get you the maximum for "A".

    I prefer to do something like this:

    =MAX(COLLECT([Col2]:[Col2], [Col1]:[Col1], [Col1]@row))

    where the formula is anywhere NOT in [Col2] and the [Col1] cell on the same row is "A".

    Good luck.

    Craig

Sign In or Register to comment.