If you want to create a new range, which is a sorted view of the input range, you would use
=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
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(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
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.
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.