The second argument is By Array, and that is the set of data we want to sort by. In our case it is the unique list of employees, identified by the spill range A2#. The first is the Array, which is the set of values that we want to return. To use the SORTBY function, we just need to identify the data for the three arguments. 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. This argument tells Excel to add up all of the duration times for the employees we just identified. In my example, that spill range reference would be A2#.Īnd finally, the sum range argument is our Duration column. For this argument, we can specify the spill range we've created that lists only the employees for the department we've identified. The second argument is the criteria it's looking for. In our example, that's the list of employee names. The first is the range of data that the function will search through. SUMIF adds the cells that are specified by a given criteria. This will rank the employees from those who spent the most time at work to those who spent the least.īefore 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. 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. These are great for displaying a list that's ranked in order of performance or chronological order (date, time). In this post we're going to take a look at two more Dynamic Array Functions, SORT and SORTBY. Return Multiple Values for a Lookup Formula in Excel with FILTER and UNIQUE.How to Use XLOOKUP for Reverse Order Search.I recommend you check out the first three posts here: This attendance report was an entry for the Excel Hash competition. This post is part 4 of a six-part series explaining how to build an interactive dashboard for attendance. 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. This includes both the desktop and web app versions of Excel. Dunder-Mifflin-Attendance-Report-Final.xlsx DownloadĬompatibility: This file uses the new Dynamic Array Functions that are only available on the latest version of Office 365.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |