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

#### lai_l

##### New Member
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 H Column M Column N Column O Column P Column Q Column V Salesperson sales stage sales revenue Start Date End Date Next Step Date Rank2 Thomas Lost \$ 10,000,000 3/1/2022​ 2/29/2024​ 1/31/2022​ 1​ Jennifer Lost \$ 14,000,000 3/1/2022​ 9/30/2024​ 1/31/2022​ 2​ George Lost \$ 9,310,680 3/5/2022​ 3/31/2024​ 2/4/2022​ 3​ Kyle Lost \$ 14,700,000 4/1/2022​ 2/28/2024​ 3/9/2022​ 4​ Jack Lost \$ 5,610,360 5/1/2022​ 4/30/2025​ 4/5/2022​ 5​ Jake Lost \$ 5,342,000 8/1/2022​ 7/31/2024​ 7/14/2022​ 6​ Reece Lost \$ 19,515,480 9/1/2022​ 8/31/2024​ 7/31/2022​ 7​ Mason Lost \$ 29,000,000 9/1/2022​ 8/31/2025​ 8/10/2022​ 8​ Harry Open \$ 10,000,000 2/1/2022​ 1/31/2024​ 1/25/2022​ 1​ Michele Open \$ 20,000,000 3/1/2022​ 2/29/2024​ 1/31/2022​ 2​ Owen Open \$ 35,000,000 4/1/2022​ 3/31/2024​ 3/31/2022​ 3​ Oscar Open \$ 47,407,457 4/1/2022​ 3/31/2024​ 2/28/2022​ 4​ David Open \$ 16,000,000 4/6/2022​ 4/5/2025​ 3/22/2022​ 5​ Callum Open \$ 42,000,000 4/6/2022​ 4/5/2025​ 3/17/2022​ 6​ Joe Open \$ 9,900,000 5/1/2022​ 4/30/2024​ 4/4/2022​ 7​ Rhys Open \$ 24,092,040 5/1/2022​ 4/30/2024​ 4/5/2022​ 8​ Charlie Open \$ 37,400,000 5/21/2022​ 5/20/2025​ 4/4/2022​ 9​ Damian Open \$ 8,000,000 7/1/2022​ 6/30/2024​ 6/17/2022​ 10​ Liam Open \$ 34,880,000 7/20/2022​ 7/19/2025​ 5/31/2022​ 11​ William Open \$ 12,000,000 8/1/2022​ 7/31/2025​ 7/13/2022​ 12​ Ethan Open \$ 7,532,760 8/18/2022​ 8/17/2024​ 7/8/2022​ 13​ Alexander Open \$ 7,000,000 9/1/2022​ 8/31/2024​ 5/31/2022​ 14​ James Open \$ 26,429,400 9/1/2022​ 8/31/2025​ 8/17/2022​ 15​ Daniel Open \$ 24,000,000 10/1/2022​ 9/30/2024​ 9/14/2022​ 16​ Dennis Open \$ 39,800,000 10/1/2022​ 9/30/2024​ 9/21/2022​ 17​ Connor Won \$ 10,759,416 10/1/2022​ 9/30/2025​ 9/15/2022​ 1​ Jacob Won \$ 32,151,240 11/1/2022​ 10/31/2025​ 10/5/2022​ 2​ Michael Won \$ 36,784,160 12/1/2022​ 11/30/2025​ 10/31/2022​ 3​ Charles Won \$ 7,783,080 1/1/2023​ 12/31/2025​ 12/22/2022​ 4​ Jackie Won \$ 25,000,000 2/1/2023​ 1/31/2025​ 1/1/2023​ 5​

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### DRSteele

##### Well-known Member
Welcome to the forum.

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

#### lai_l

##### New Member
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.

##### Well-known Member
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)

#### DRSteele

##### Well-known Member
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.

#### Marcelo Branco

##### MrExcel MVP
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.

#### Marcelo Branco

##### MrExcel MVP
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.

#### lai_l

##### New Member
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!

Replies
3
Views
325
Replies
1
Views
135
Replies
2
Views
125
Replies
3
Views
74
Replies
0
Views
292

1,186,808
Messages
5,959,933
Members
438,455
Latest member
Beverly Jarrell

### 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?

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