FILTER with SORT for a live leaderboard
IntermediateIn sales reviews, the goal is rarely to scroll through the full team table. Usually you want to see only the reps who have beaten their quota and know who is performing best. With dynamic array formulas, you can build a live leaderboard that handles both the filtering and the ranking in a single formula.
SORT and FILTER can be nested to combine their behavior. The general pattern looks like this:
=SORT(FILTER(array, include, [if_empty]), sort_index, sort_order)
- The inner FILTER returns only the rows that pass your condition
- The outer SORT reorders those matching rows by one of the columns
Your sheet contains a sales performance table in A1:D10 with columns for Rep, Region, Quota, and Total sales. Build a leaderboard that shows only the reps whose total sales exceed their quota, ranked from highest sales to lowest.
Your task:
Enter one spill formula in A14 that produces the full leaderboard with all four columns. The result should include only qualifying reps and should sort them by total sales descending. Let the output spill automatically through the answer area below.
Need some help?
Hint 1
In the FILTER condition, compare the Total sales column to the Quota column row by row. A rep qualifies when their sales number is greater than their quota number.
Hint 2
After FILTER returns the matching rows, wrap it in SORT and set sort_index to the column position of Total sales within the filtered result (not its original column letter) with sort_order set to -1 for descending.
Hint 3
The FILTER array is the full four-column table. In the include argument, use column D > column C for the row-by-row comparison. Then the SORT uses 4 as the sort_index because Total sales is the fourth column of the filtered table.
FILTER with SORT for a live leaderboard
IntermediateIn sales reviews, the goal is rarely to scroll through the full team table. Usually you want to see only the reps who have beaten their quota and know who is performing best. With dynamic array formulas, you can build a live leaderboard that handles both the filtering and the ranking in a single formula.
SORT and FILTER can be nested to combine their behavior. The general pattern looks like this:
=SORT(FILTER(array, include, [if_empty]), sort_index, sort_order)
- The inner FILTER returns only the rows that pass your condition
- The outer SORT reorders those matching rows by one of the columns
Your sheet contains a sales performance table in A1:D10 with columns for Rep, Region, Quota, and Total sales. Build a leaderboard that shows only the reps whose total sales exceed their quota, ranked from highest sales to lowest.
Your task:
Enter one spill formula in A14 that produces the full leaderboard with all four columns. The result should include only qualifying reps and should sort them by total sales descending. Let the output spill automatically through the answer area below.
Need some help?
Hint 1
In the FILTER condition, compare the Total sales column to the Quota column row by row. A rep qualifies when their sales number is greater than their quota number.
Hint 2
After FILTER returns the matching rows, wrap it in SORT and set sort_index to the column position of Total sales within the filtered result (not its original column letter) with sort_order set to -1 for descending.
Hint 3
The FILTER array is the full four-column table. In the include argument, use column D > column C for the row-by-row comparison. Then the SORT uses 4 as the sort_index because Total sales is the fourth column of the filtered table.