Forum Discussion
Using the RANK.EQ & SUMPRODUCT functions but with a second and third tiebreaker.
I came across this video from the Mr. Excel/ ExcelIsFun series (https://www.youtube.com/watch?v=QkOwkbdaruQ ) where I learned how to use the RANK.EQ function to easily place rankings in rows of data with one determinant factor, and the SUMPRODUCT function to rank the rows when there's a tie.
In a project I'm working on, each cell has this "=RANK.EQ(H2,$H$2:$H$199,0)+SUMPRODUCT(--(H2=$H$2:$H$199),--(D2<$D$2:$D$199))" so column H is the first number the cells are ranked by and column D is when there's a tie.
However, I can't figure out how to input a condition and break a tie when D AND H have the same value, for example: if two cells have the same values for H and D, and I wanted column 'E' to be the next determinant factor, how would I work that in the functions, besides just replacing D with E in individual ties? I want to know how to work the function so I can keep the priority of the tiebreakers in the order I want them within the functions.