Google Sheets: How to Find Max Value by Group


Often you may want to find the max value of some dataset in Google Sheets based on a category or group.

For example, suppose we have the following dataset and we’d like to find the max value of “points” for each team:

The following step-by-step example shows how to do so.

Step 1: Enter the Data

First, enter the data values into Google Sheets:

Step 2: Find the Unique Groups

Next, we need to use the =UNIQUE() function to produce a list of unique team names.

In our example, we’ll type the following formula in cell D2:

=UNIQUE(A2:A16)

This will produce a list of unique teams:

Step 3: Find the Max Value by Group

Next, we will use the following formula to find the max points scored by each team:

=ArrayFormula(MAX(IF(A:A=D2,B:B)))

We will type this formula into cell E2 and then drag it down to the remaining cells in column E:

max value by group in Google Sheets

From the results we can see:

  • The max points scored by players on the Mavs is 26.
  • The max points scored by players on the Warriors is 19.
  • The max points scored by players on the Lakers is 33.
  • The max points scored by players on the Heat is 19.
  • The max points scored by players on the Celtics is 29.

Note: To calculate the minimum points scored by each team, simply replace the MAX in the formula with MIN.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Group Data by Week in Google Sheets
How to Group Data by Month in Google Sheets
How to Use Group By in Google Sheets Query

Leave a Reply

Your email address will not be published.