Get Max value with criteria check

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



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!