Excel Rank by multiple criteria (date, income and text)

lai_l

New Member
Joined
Dec 11, 2021
Messages
3
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
Hi all

I have the a lot of data to extract. Here is part of it.
The following column names are simply copied from my working file in excel.


I would like to rank according to criteria below
-- within respective sales stage of [lost], [won], [open] → Start Date (from earliest to final one) → then, ranked by sales revenue (from largest to smallest).


And now I can get result in column V - rank 2 because of the formula =COUNTIFS($M$2:$M$10000,M2,$O$2:$O$10000,"<"&O2)+COUNTIFS(M$2:M2,M2,O$2:O2,O2)
As seen below, if two records happen on same date, the function rank the least sales revenue first. How should I fix it?

I watched a lot of videos and posts today and even tried sumproduct. It doesn't help. Thanks in advance.


Column HColumn MColumn NColumn OColumn PColumn QColumn V
Salespersonsales stagesales revenueStart DateEnd DateNext Step DateRank2
ThomasLost$ 10,000,000
3/1/2022​
2/29/2024​
1/31/2022​
1
JenniferLost$ 14,000,000
3/1/2022​
9/30/2024​
1/31/2022​
2
GeorgeLost$ 9,310,680
3/5/2022​
3/31/2024​
2/4/2022​
3​
KyleLost$ 14,700,000
4/1/2022​
2/28/2024​
3/9/2022​
4​
JackLost$ 5,610,360
5/1/2022​
4/30/2025​
4/5/2022​
5​
JakeLost$ 5,342,000
8/1/2022​
7/31/2024​
7/14/2022​
6​
ReeceLost$ 19,515,480
9/1/2022​
8/31/2024​
7/31/2022​
7
MasonLost$ 29,000,000
9/1/2022​
8/31/2025​
8/10/2022​
8
HarryOpen$ 10,000,000
2/1/2022​
1/31/2024​
1/25/2022​
1​
MicheleOpen$ 20,000,000
3/1/2022​
2/29/2024​
1/31/2022​
2​
OwenOpen$ 35,000,000
4/1/2022​
3/31/2024​
3/31/2022​
3​
OscarOpen$ 47,407,457
4/1/2022​
3/31/2024​
2/28/2022​
4
DavidOpen$ 16,000,000
4/6/2022​
4/5/2025​
3/22/2022​
5
CallumOpen$ 42,000,000
4/6/2022​
4/5/2025​
3/17/2022​
6
JoeOpen$ 9,900,000
5/1/2022​
4/30/2024​
4/4/2022​
7
RhysOpen$ 24,092,040
5/1/2022​
4/30/2024​
4/5/2022​
8
CharlieOpen$ 37,400,000
5/21/2022​
5/20/2025​
4/4/2022​
9​
DamianOpen$ 8,000,000
7/1/2022​
6/30/2024​
6/17/2022​
10​
LiamOpen$ 34,880,000
7/20/2022​
7/19/2025​
5/31/2022​
11​
WilliamOpen$ 12,000,000
8/1/2022​
7/31/2025​
7/13/2022​
12​
EthanOpen$ 7,532,760
8/18/2022​
8/17/2024​
7/8/2022​
13​
AlexanderOpen$ 7,000,000
9/1/2022​
8/31/2024​
5/31/2022​
14​
JamesOpen$ 26,429,400
9/1/2022​
8/31/2025​
8/17/2022​
15​
DanielOpen$ 24,000,000
10/1/2022​
9/30/2024​
9/14/2022​
16​
DennisOpen$ 39,800,000
10/1/2022​
9/30/2024​
9/21/2022​
17​
ConnorWon$ 10,759,416
10/1/2022​
9/30/2025​
9/15/2022​
1​
JacobWon$ 32,151,240
11/1/2022​
10/31/2025​
10/5/2022​
2​
MichaelWon$ 36,784,160
12/1/2022​
11/30/2025​
10/31/2022​
3​
CharlesWon$ 7,783,080
1/1/2023​
12/31/2025​
12/22/2022​
4​
JackieWon$ 25,000,000
2/1/2023​
1/31/2025​
1/1/2023​
5​
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Welcome to the forum.

What you seek is ranking with tiebreakers. Read this article and let us know if it helps.
 
Upvote 0
Welcome to the forum.

What you seek is ranking with tiebreakers. Read this article and let us know if it helps.
Hi Sir

Thanks for your comment and I followed the steps. Unluckily, the order of magnitude - sales revenue is 14 digits.
Thus, the excel shows error in the row of "multiplier".

Are there any ways to rank "sales revenue" & "date" at the same time easily?

Thanks a lot.
 
Upvote 0
Hi Lai_L,

While the COUNTIF method works well when ranking a single attribute I don't see how you could rank days and dollars with COUNTIF.

There is a phrase "Another day, another dollar" but it appears with an average transaction value of $20,713,269 over the 365 days covered by Start Date range then in your case the phrase may be better expressed as "Another day, another $500,000 dollars". :)

I would use one of the RANK functions and decide how dollars relate to days. In my example I've used 1 day=$500,000 and calculated days as the maximum of the Start Date range minus this transaction Start Date (so the latest transaction will be zero days so will be ranked solely on Sales Revenue).

RANK doesn't let you select a subset of cells to rank so I've split the Won, Lost and Open into three columns so the RANK.EQ will work. The Rank 2 column then pulls them back into one column as per your example based on which type they are, Won, Lost or Open.

This approach greatly reduces the chance of a duplicate rank (in fact there are none using your sample data) but it does require you to decide how days relate to dollars.

lai_l.xlsx
HMNOPQVWXY
1Salespersonsales stagesales revenueStart DateEnd DateNext Step DateRank2WonLostOpen
2ThomasLost$10,000,0003/1/20222/29/20241/31/20222 178500000 
3JenniferLost$14,000,0003/1/20229/30/20241/31/20221 182500000 
4GeorgeLost$9,310,6803/5/20223/31/20242/4/20223 175810680 
5KyleLost$14,700,0004/1/20222/28/20243/9/20224 167700000 
6JackLost$5,610,3605/1/20224/30/20254/5/20225 143610360 
7JakeLost$5,342,0008/1/20227/31/20247/14/20227 97342000 
8ReeceLost$19,515,4809/1/20228/31/20247/31/20228 96015480 
9MasonLost$29,000,0009/1/20228/31/20258/10/20226 105500000 
10HarryOpen$10,000,0002/1/20221/31/20241/25/20222  192500000
11MicheleOpen$20,000,0003/1/20222/29/20241/31/20224  188500000
12OwenOpen$35,000,0004/1/20223/31/20243/31/20225  188000000
13OscarOpen$47,407,4574/1/20223/31/20242/28/20221  200407457
14DavidOpen$16,000,0004/6/20224/5/20253/22/20226  166500000
15CallumOpen$42,000,0004/6/20224/5/20253/17/20222  192500000
16JoeOpen$9,900,0005/1/20224/30/20244/4/20229  147900000
17RhysOpen$24,092,0405/1/20224/30/20244/5/20228  162092040
18CharlieOpen$37,400,0005/21/20225/20/20254/4/20227  165400000
19DamianOpen$8,000,0007/1/20226/30/20246/17/202211  115500000
20LiamOpen$34,880,0007/20/20227/19/20255/31/202210  132880000
21WilliamOpen$12,000,0008/1/20227/31/20257/13/202212  104000000
22EthanOpen$7,532,7608/18/20228/17/20247/8/202215  91032760
23AlexanderOpen$7,000,0009/1/20228/31/20245/31/202217  83500000
24JamesOpen$26,429,4009/1/20228/31/20258/17/202213  102929400
25DanielOpen$24,000,00010/1/20229/30/20249/14/202216  85500000
26DennisOpen$39,800,00010/1/20229/30/20249/21/202214  101300000
27ConnorWon$10,759,41610/1/20229/30/20259/15/2022272259416  
28JacobWon$32,151,24011/1/202210/31/202510/5/2022178151240  
29MichaelWon$36,784,16012/1/202211/30/202510/31/2022367784160  
30CharlesWon$7,783,0801/1/202312/31/202512/22/2022523283080  
31JackieWon$25,000,0002/1/20231/31/20251/1/2023425000000  
1st (3)
Cell Formulas
RangeFormula
V2:V31V2=CHOOSE(MATCH(MAX($W2:$Y2),$W2:$Y2,0),RANK.EQ(W2,$W$2:$W$10000),RANK.EQ(X2,$X$2:$X$10000),RANK.EQ(Y2,$Y$2:$Y$10000))
W2:Y31W2=IF($M2<>W$1,"",((MAX($O$2:$O$10000)-$O2)*500000)+$N2)
 
Upvote 0
Hi Sir

Thanks for your comment and I followed the steps. Unluckily, the order of magnitude - sales revenue is 14 digits.
Thus, the excel shows error in the row of "multiplier".

Are there any ways to rank "sales revenue" & "date" at the same time easily?

Thanks a lot.
You could consider rounding the revenue field to thousands. That would save three of the fourteen. Or millions, which would save six. Or millions with one decimal place, which would save five.
 
Upvote 0
Maybe...

=COUNTIFS(M$2:M$10000,M2,O$2:O$10000,"<"&O2)+COUNTIFS(M$2:M$10000,M2,O$2:O$10000,O2,N$2:N$10000,">"&N2)+1
copy down

M.
 
Upvote 0
Are these the expected results?

Pasta1
HIJKLMNOPQR
1Salespersonsales stagesales revenueStart DateEnd DateNext Step DateRank2
2ThomasLost100000003/1/20222/29/20241/31/20222
3JenniferLost140000003/1/20229/30/20241/31/20221
4GeorgeLost93106803/5/20223/31/20242/4/20223
5KyleLost147000004/1/20222/28/20243/9/20224
6JackLost56103605/1/20224/30/20254/5/20225
7JakeLost53420008/1/20227/31/20247/14/20226
8ReeceLost195154809/1/20228/31/20247/31/20228
9MasonLost290000009/1/20228/31/20258/10/20227
10HarryOpen100000002/1/20221/31/20241/25/20221
11MicheleOpen200000003/1/20222/29/20241/31/20222
12OwenOpen350000004/1/20223/31/20243/31/20224
13OscarOpen474074574/1/20223/31/20242/28/20223
14DavidOpen160000004/6/20224/5/20253/22/20226
15CallumOpen420000004/6/20224/5/20253/17/20225
16JoeOpen99000005/1/20224/30/20244/4/20228
17RhysOpen240920405/1/20224/30/20244/5/20227
18CharlieOpen374000005/21/20225/20/20254/4/20229
19DamianOpen80000007/1/20226/30/20246/17/202210
20LiamOpen348800007/20/20227/19/20255/31/202211
21WilliamOpen120000008/1/20227/31/20257/13/202212
22EthanOpen75327608/18/20228/17/20247/8/202213
23AlexanderOpen70000009/1/20228/31/20245/31/202215
24JamesOpen264294009/1/20228/31/20258/17/202214
25DanielOpen2400000010/1/20229/30/20249/14/202217
26DennisOpen3980000010/1/20229/30/20249/21/202216
27ConnorWon1075941610/1/20229/30/20259/15/20221
28JacobWon3215124011/1/202210/31/202510/5/20222
29MichaelWon3678416012/1/202211/30/202510/31/20223
30CharlesWon77830801/1/202312/31/202512/22/20224
31JackieWon250000002/1/20231/31/20251/1/20235
32
Plan1
Cell Formulas
RangeFormula
R2:R31R2=COUNTIFS(M$2:M$10000,M2,O$2:O$10000,"<"&O2)+COUNTIFS(M$2:M$10000,M2,O$2:O$10000,O2,N$2:N$10000,">"&N2)+1


M.
 
Upvote 0
Solution
Thanks for all of your solutions. I got one of the best solution now. I will familiarize myself with logic of all solutions as well. cheers!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top