Table of Contents
To rank items by multiple criteria in Excel, you can use the RANK function which allows you to assign a numerical ranking to a set of data based on specific criteria. This function can be used to rank items based on multiple criteria such as numerical values, text, or dates. By using the RANK function, you can easily organize and compare items based on different factors and determine their relative positions. This can be particularly useful in situations where you need to prioritize or analyze large sets of data. Additionally, Excel also offers other tools such as conditional formatting and sorting that can further assist in ranking items by multiple criteria. Overall, utilizing these features in Excel can help you efficiently and accurately rank items based on various criteria.
Rank Items by Multiple Criteria in Excel
You can use a combination of the RANK.EQ() function and the COUNTIFS() function in Excel to rank items by multiple criteria.
The following example shows how to use these functions to rank items in a list by multiple criteria in Excel.
Example: Rank by Multiple Criteria in Excel
Suppose we have the following dataset in Excel that shows the total points and assists for eight different basketball players.
Now suppose we’d like to rank each player using the following criteria:
- First, rank each player based on points.
- Then, rank each player based on assists.
We can use the following formula to perform this multiple criteria ranking:
=RANK.EQ($B2, $B$2:$B$9) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, ">" &$C2)
We can type this formula into cell D2 of our spreadsheet, then copy and paste the formula down to every other cell in column D:
From the output we can see that Andy receives a rank of 1 because he is tied for the most points with Bernard. However, Andy has more assists than Bernard, so he receives a rank of 1 while Bernard receives a rank of 2.
Each player is ranked in a similar manner, first based on their total points and then based on their total assists.
If we instead would like to perform multiple criteria ranking in reverse order (the “best” player gets the highest ranking) then we can use the following formula:
=RANK.EQ($B2, $B$2:$B$9, 1) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, "<" &$C2)
We can type this formula into cell D2, then copy and paste the formula down to every other cell in column D:
Similarly, Bernard now has a ranking of 7. And so on.
The following tutorials explain how to perform other common functions in Excel:
Cite this article
stats writer (2024). How can I rank items by multiple criteria in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-rank-items-by-multiple-criteria-in-excel/
stats writer. "How can I rank items by multiple criteria in Excel?." PSYCHOLOGICAL SCALES, 6 May. 2024, https://scales.arabpsychology.com/stats/how-can-i-rank-items-by-multiple-criteria-in-excel/.
stats writer. "How can I rank items by multiple criteria in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-rank-items-by-multiple-criteria-in-excel/.
stats writer (2024) 'How can I rank items by multiple criteria in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-rank-items-by-multiple-criteria-in-excel/.
[1] stats writer, "How can I rank items by multiple criteria in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, May, 2024.
stats writer. How can I rank items by multiple criteria in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.