Sort products by two columns with SORTBY
IntermediateSORTBY is useful when a report needs more than one sorting rule at the same time. In a sales review, you might want products grouped by category first, then ranked by performance inside each category. Unlike SORT, which uses a column position from the returned array, SORTBY points directly to the ranges that control the order, so it is better suited to multi-level sorting.
SORTBY uses this structure:
=SORTBY(array, by_array1, sort_order1, [by_array2], [sort_order2])
The array is the full range you want to return. Each by_array is a column or row that controls the order, and each sort_order is 1 for ascending or -1 for descending.
In this sheet, you are preparing a product view for a sales review. The output should match the sorting rule described in A12. Because the revenue ranking needs to restart inside each category group, you will need to decide which sort key should control the grouping first and which one should handle the within-group ranking.
Your task:
Enter one SORTBY formula in A13 that returns the full product table in the correct order. Use the original table as the returned array, choose the sort levels that will create the category groups and the revenue ranking, and let the result spill automatically through the output area.
Need some help?
Hint 1
SORTBY applies sort levels from left to right. Think about which column must create the broad category groups before any revenue comparison happens inside those groups.
Hint 2
Return the entire table, not a single column. To match the label in A12, one sort level needs alphabetical text order (ascending) and the other needs largest-to-smallest numeric order (descending).
Sort products by two columns with SORTBY
IntermediateSORTBY is useful when a report needs more than one sorting rule at the same time. In a sales review, you might want products grouped by category first, then ranked by performance inside each category. Unlike SORT, which uses a column position from the returned array, SORTBY points directly to the ranges that control the order, so it is better suited to multi-level sorting.
SORTBY uses this structure:
=SORTBY(array, by_array1, sort_order1, [by_array2], [sort_order2])
The array is the full range you want to return. Each by_array is a column or row that controls the order, and each sort_order is 1 for ascending or -1 for descending.
In this sheet, you are preparing a product view for a sales review. The output should match the sorting rule described in A12. Because the revenue ranking needs to restart inside each category group, you will need to decide which sort key should control the grouping first and which one should handle the within-group ranking.
Your task:
Enter one SORTBY formula in A13 that returns the full product table in the correct order. Use the original table as the returned array, choose the sort levels that will create the category groups and the revenue ranking, and let the result spill automatically through the output area.
Need some help?
Hint 1
SORTBY applies sort levels from left to right. Think about which column must create the broad category groups before any revenue comparison happens inside those groups.
Hint 2
Return the entire table, not a single column. To match the label in A12, one sort level needs alphabetical text order (ascending) and the other needs largest-to-smallest numeric order (descending).