How to Sort with a Formula in Excel Using SORT and SORTBY Functions

Bottom Line: Learn how to use the SORT and SORTBY functions to return a sorted range based on one or more criteria.

Skill Level: Intermediate

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

Download the Excel File

The file that I work with in the video can be found below.  You can also download the “Follow Along” version if you'd like to practice building out the report.

Dunder Mifflin Attendance Report - Follow Along.xlsx (24.4 KB)

Dunder Mifflin Attendance Report - Final.xlsx (166.6 KB)

Compatibility: This file uses the new Dynamic Array Functions that are only available on the latest version of Office 365. This includes both the desktop and web app versions of Excel.

I have also posted a bonus episode in this series that covers how to make the dashboard with older versions of Excel using pivot tables instead.

Building an Attendance Dashboard

This post is part 4 of a six-part series explaining how to build an interactive dashboard for attendance.  This attendance report was an entry for the Excel Hash competition.  I recommend you check out the first three posts here:

  1. How to Use the XOR Function
  2. How to Use XLOOKUP for Reverse Order Search
  3. Return Multiple Values for a Lookup Formula in Excel with FILTER and UNIQUE

Sort and Rank

In this post we're going to take a look at two more Dynamic Array Functions, SORT and SORTBY.  These are great for displaying a list that's ranked in order of performance or chronological order (date, time).

In the case of our example, we are going to sort employees based on the time they spent in the office (duration) for the week. This will rank the employees from those who spent the most time at work to those who spent the least.

Before we get sorting, however, let me briefly show you how I added all of the duration times together for each employee using the SUMIF function.

The SUMIF Function

SUMIF adds the cells that are specified by a given criteria.  It has three arguments.  The first is the range of data that the function will search through.  In our example, that's the list of employee names.

The second argument is the criteria it's looking for.  For this argument, we can specify the spill range we've created that lists only the employees for the department we've identified.  In my example, that spill range reference would be A2#.

And finally, the sum range argument is our Duration column.  This argument tells Excel to add up all of the duration times for the employees we just identified.  So our final formula looks like this:

=SUMIF(tblData[Employee],A2#, tblData[Duration])

SUMIF Function

The SORTBY Function

Now that we've summed up all of the hours for each employee, we can sort the employees by the number of hours that they worked.

To use the SORTBY function, we just need to identify the data for the three arguments.  The first is the Array, which is the set of values that we want to return.  In our case it is the unique list of employees, identified by the spill range A2#.

The second argument is By Array, and that is the set of data we want to sort by.  We want to sort by the total time each person spent at work, so we use the Sum of Time data, identified by the spill range B2#.

The third and final argument, Sort Order, just specifies how to sort the data. Our choices are ascending or descending.  We want to sort in descending order (greatest to smallest), which is represented by -1.

So our SORTBY function would read as follows:

=SORTBY(A2#,B2#,-1)

SORTBY Function

The SORT Function

In addition to ranking the employees from most hours of attendance to least, it would also be helpful to list those corresponding hours next to each name.  We could accomplish that in many ways, but we will use the SORT function.  This is another Dynamic Array Function that returns multiple values in a spill range.

To use SORT, you just need to identify the range that you want to sort, and the order you want to sort it in.  (The second argument in the SORT function, sort index, is not needed for our example.)

Our simple formula would look like this:

=SORT (D2#,,-1)

SORT Function

Other Metrics

The other formulas on the sheet are fairly basic and are outlined below.

  • Sum: =SUM(D2#)
  • Avg: =AVERAGE(D2#)
  • Good Label: =IF($I$3>=$I$5,$I$3,””)
  • Bad Label: =IF($I$3<$I$5,$I$3,””)

Other Metrics Formulas

Conclusion

In our next video, I will show you how to build out the Attendance Dashboard, now that we have taken care of the calculations and functions behind the scenes.

If you have any questions about using SORT, SORTBY, or SUMIF, please leave a comment. Thanks for reading!

4 comments

Your email address will not be published. Required fields are marked *

  • Amazing Jon, I have been following you for a long time now and have without any exaggeration learnt something new with each and every post.
    Thank you, Jon, for your invaluable and easy to follow videos.

  • Hi John,

    No matter how skilled one is, one always learn new tips. I never realized that the “#” tag was available in Excel.

    Thanks John for providing such nice tips. Great video!

    Keep up the good work coming.

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly