Google Sheets Sorting – How to Rank and Sort by Multiple Columns

google sheets

So, my data sheet looks similar to this:

       Team #  Rank1   Rank2
---------------------------------
  1 |  Team 1    1    1,234.567
  2 |  Team 2    2    1,234.567
  3 |  Team 3    2    1,000.000
  4 |  Team 4    3    4,000.000
  5 |  Team 5    0    9,000.000

Is there a way to rank, and subsequently, sort this, where rank 1 is more important than rank 2, so it would end up with something looking like this?

      Team #  Rank1    Rank2     FRank
--------------------------------------
  1 | Team 4    3    4,000.000     1
  2 | Team 2    2    1,234.567     2
  3 | Team 3    2    1,000.000     3
  4 | Team 1    1    1,234.567     4
  5 | Team 5    0    9,000.000     5

Best Answer

If you want to create a new range, which is a sorted view of the input range, you would use =SORT. The =SORT function can take multiple columns as parameters:

=SORT(area, columnA, ascendingA, columnB, ascendingB)

In your case, you want to sort by column B in descending order, then by column C in ascending order. Given you have your data in column A through C, this should do what you want:

=SORT(A2:C; 2; FALSE; 3; TRUE)

If you want to output the rank of a team in the sorted result set, the easiest way is to use the row number, =ROW.

So =ROW(H2) would yield 2. Knowing that, we could say that the 1-based rank of a row is =ROW(any row in the result) - 1. If we have the sorted result table in H2:J, we can output the rank of each row:

=ARRAYFORMULA(ROW(H2:H6) - 1)

I set up an example spreadsheet to demonstrate, feel free to take a look and/or copy it.
And check the documentation for =SORT and =ROW.

If you want to sort the input range in-place, you can't use a formula - because a formula needs an input range and an output range, which must be different.

For a one-time sort, select the input data (including the header row) and click Tools → Sort range. The dialog that appears has a checkbox for Data has header row - click it.

Specify a sort column (Rank 1), and descending order (Z→A). Then click Add another sort column, and select Rank 2, and ascending (A→Z).

This will sort the range - but only once, so if you alter the data, you will need to repeat the sort procedure.

The third option is to create a Filter view. Select the data, and click _Data → Filter views → Create new Filter view.

Screenshot of the Filter view Give the filter a name. Then, for each column you want to use in your sort, click the down arrow icon (2). Specify the sort order for each column. Close the filter view by clicking the X in the upper right corner.

This will bring you back to your input data (unsorted). To go back to the sorted view, click _Data → Filter views → [your filter view].

Also in this case, you need to repeat the sort procedure when data changes.

Related Topic