Forum Discussion
Ranking 3 columns
Hi all, I have a tracker that I'm trying to create a ranking for.
So far I've been using:
=RANK.EQ($S3,$S$3:$S$15)+COUNTIFS($S$3:$S$15,$S3,$T$3:$T$15,">"&$T3)
Which ranks Total AOV against Orders, if I'm correct.
What I want to do is also rank orders per hour, but I'm struggling to see how to add that in.
| TOTAL AOV | ORDERS | ORDER PER HOUR | RANKING |
| £33.26 | 391 | 11 | 1 |
| £23.36 | 295 | 8 | 2 |
| £22.90 | 176 | 8 | 3 |
1 Reply
To achieve ranking based on multiple criteria (Total AOV, Orders, and Orders per Hour) in Excel, you can modify the formula to include all these criteria. Here is how you can adjust your ranking formula:
Assuming your data is structured as follows, with headers in row 2 and data from row 3 onwards:
- Column S: Total AOV
- Column T: Orders
- Column U: Orders per Hour
And you want to calculate the ranking in column V.
Formula Explanation:
The formula will rank based on Total AOV first. If Total AOV values are the same, it will then consider Orders. If both Total AOV and Orders are the same, it will finally consider Orders per Hour.
=IFERROR(RANK.EQ($S3,$S$3:$S$15)
+ COUNTIFS($S$3:$S$15, $S3, $T$3:$T$15, ">" & $T3)
+ COUNTIFS($S$3:$S$15, $S3, $T$3:$T$15, $T3, $U$3:$U$15, ">" & $U3),"")
Copy this formula into cell V3 and then drag it down through V15 (or adjust the range accordingly based on your data).
Make sure your data is consistent and correctly formatted (e.g., numbers instead of text where necessary) for the formula to work correctly.
This formula should effectively rank your data based on Total AOV, Orders, and Orders per Hour, providing a comprehensive ranking that considers all three criteria. The text and steps were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
