Welcome!

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

Formulas

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

INDEX/MATCH with the range on MATCH is multiple columns
I have one sheet that is tracking PTO where a user has entered the days off. The form allows them to enter a start and end date for their PTO and enters a single record into the sheet. I have a second sheet that I am looking to pull that data into, but there is only one record per person (column in this sheet where the…
I am trying to add the number of passengers per month per year
I am trying to get the passenger count per month per year, and I can't seem to get the formula correct. I need to add the number of passengers for each month per year. If anyone could assist me with this, I would greatly appreciate it. Thanks!
Formula Wormhole - Count ifs distinct with multiple range requests
Date Report Partner CBO Name 01/12/25 Children's org 05/15/25 Foodbank 08/30/25 Children's org 09/11/25 Chamber 11/25/25 Digital Bus 08/21/24 Digital Bus South 01/15/26 Children's org 01/12/24 Foodbank Sister 02/15/24 For the children org 07/01/26 Digital Bus #2 08/05/26 Family Center 12/01/26 Family Center I am trying to…