Get Max value with criteria check

Ramesh Rabadiya

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

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