Welcome to Excel Avon
SORTBY Formula in Excel
The Excel SORTBY function sorts the contents of a range or array based on the values of another range or array. Sorting can be done by one or more columns. Let’s solve the example using SORTBY formula.
Formula
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)
Arguments
Array – the range of cells or array of values to be sorted.
By_array1 – Range or array to sort by.
Sort_order1 – (optional) the sorting order:
-
-
-
- 1 or omitted (default) – ascending
- -1 – descending
-
-
By_array – (optional) additional array
Sort_order2 – (optional) order pairs to use for sorting.
Example Of SORTBY Formula
DOWNLOAD USED EXCEL FILE FROM HERE>>
We will tell by using two methods first sort by single range and second sort by multiple range.
SORTBY by Single Range Ascending
As you can see in the image given below that the name and salary are given in the data sheet. In the first example we will use single range ascending.
In the given image, we will first take the column containing the total; and then apply the formula
=SORTBY(Table1,Table1[Total],1)
In the formula you can see that we have written 1 which is being used for ascending order.
After using the formula, we will press enter and then you can see the result we have, there is a change in the column with the total as the small amount is in the upper and the big lower ascending order.
Result of ascending is known now let’s go in descending order
SORTBY by Single Range Descending
Single range descending order again we will apply formula and find result.
In the above image you can see that there are 2 columns first in which name is written in which total amount is written and the table is without any order.
So we have to do it in descending order which is the total of the column containing the amount.
Now I will apply SORTBY Formula
=SORTBY(Table1,Table1[Total],-1)
In the formula you can see that we have written -1 which is being used for descending order.
After using the formula, we will press Enter and then you can see the result we have, with the total in the column changed because the larger amount is in the upper and the smaller amount will be at the bottom in descending order.
Result of Descending is known
SORTBY by multiple range ascending order
SORTBY formula For Multiple Range Ascending Order we have created an excel data table in which there is a column named Age Total, Total which is an amount
In the above image you can see that the first 3 columns are the age in the second and the third column is the total in which the total amount is written and the table is in random order.
So we have to do it in ascending order and array we have two given first array name and second array will be edge then we have to do name and age in ascending order
Now I will apply SORTBY Formula
=SORTBY(Table13,Table13[Name],1,Table13[Age],1)
In the formula you can see that we have written 1 which is being used for Ascending order.
After using SORTBY formula, we will press enter and then you can see the result we have, as per name in column like first name starting with ‘A’ and last starting with ‘Z’ but after name Age wise the result will come because multiple array is selected here.
Finally we got the result of Multiple Range Ascending Order
SORTBY by multiple range Descending order
SORTBY Formula Multiple Range Descending Order We have created an excel data table which has a column named Name, Age, Total, Total which is an amount.
In the above image you can see that the first column which is name second is age and third column is total in which total amount is written and the table is in random order. So we have to do it in descending order and array we have two given first array name and second array will be age then we have to do name and age in descending order.
Now I will apply SORTBY Formula
=SORTBY(Table13,Table13[Name],-1,Table13[Age],-1)
In the formula you can see that we have written -1 which is being used for descending order.
After using SORTBY formula, we will press enter and then you can see the result we have, like as per name in column first name starts with ‘z’ and last starts with ‘A’ but after name with age As per result will come because multiple array is selected here.
Finally we got the result of Multiple Range Descending Order.
DOWNLOAD USED EXCEL FILE FROM HERE>>
So I hope you have understood this formula and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
You can also see well-explained video here
