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

[lai_l]

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​

 

[DRSteele]

Welcome to the forum.

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

 

[lai_l]

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.

 

[Toadstool]

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
	H					M	N	O	P	Q					V	W	X	Y
1	Salesperson					sales stage	sales revenue	Start Date	End Date	Next Step Date					Rank2	Won	Lost	Open
2	Thomas					Lost	$10,000,000	3/1/2022	2/29/2024	1/31/2022					2		178500000
3	Jennifer					Lost	$14,000,000	3/1/2022	9/30/2024	1/31/2022					1		182500000
4	George					Lost	$9,310,680	3/5/2022	3/31/2024	2/4/2022					3		175810680
5	Kyle					Lost	$14,700,000	4/1/2022	2/28/2024	3/9/2022					4		167700000
6	Jack					Lost	$5,610,360	5/1/2022	4/30/2025	4/5/2022					5		143610360
7	Jake					Lost	$5,342,000	8/1/2022	7/31/2024	7/14/2022					7		97342000
8	Reece					Lost	$19,515,480	9/1/2022	8/31/2024	7/31/2022					8		96015480
9	Mason					Lost	$29,000,000	9/1/2022	8/31/2025	8/10/2022					6		105500000
10	Harry					Open	$10,000,000	2/1/2022	1/31/2024	1/25/2022					2			192500000
11	Michele					Open	$20,000,000	3/1/2022	2/29/2024	1/31/2022					4			188500000
12	Owen					Open	$35,000,000	4/1/2022	3/31/2024	3/31/2022					5			188000000
13	Oscar					Open	$47,407,457	4/1/2022	3/31/2024	2/28/2022					1			200407457
14	David					Open	$16,000,000	4/6/2022	4/5/2025	3/22/2022					6			166500000
15	Callum					Open	$42,000,000	4/6/2022	4/5/2025	3/17/2022					2			192500000
16	Joe					Open	$9,900,000	5/1/2022	4/30/2024	4/4/2022					9			147900000
17	Rhys					Open	$24,092,040	5/1/2022	4/30/2024	4/5/2022					8			162092040
18	Charlie					Open	$37,400,000	5/21/2022	5/20/2025	4/4/2022					7			165400000
19	Damian					Open	$8,000,000	7/1/2022	6/30/2024	6/17/2022					11			115500000
20	Liam					Open	$34,880,000	7/20/2022	7/19/2025	5/31/2022					10			132880000
21	William					Open	$12,000,000	8/1/2022	7/31/2025	7/13/2022					12			104000000
22	Ethan					Open	$7,532,760	8/18/2022	8/17/2024	7/8/2022					15			91032760
23	Alexander					Open	$7,000,000	9/1/2022	8/31/2024	5/31/2022					17			83500000
24	James					Open	$26,429,400	9/1/2022	8/31/2025	8/17/2022					13			102929400
25	Daniel					Open	$24,000,000	10/1/2022	9/30/2024	9/14/2022					16			85500000
26	Dennis					Open	$39,800,000	10/1/2022	9/30/2024	9/21/2022					14			101300000
27	Connor					Won	$10,759,416	10/1/2022	9/30/2025	9/15/2022					2	72259416
28	Jacob					Won	$32,151,240	11/1/2022	10/31/2025	10/5/2022					1	78151240
29	Michael					Won	$36,784,160	12/1/2022	11/30/2025	10/31/2022					3	67784160
30	Charles					Won	$7,783,080	1/1/2023	12/31/2025	12/22/2022					5	23283080
31	Jackie					Won	$25,000,000	2/1/2023	1/31/2025	1/1/2023					4	25000000
1st (3)

Cell Formulas
Range	Formula
V2:V31	V2	=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:Y31	W2	=IF($M2<>W$1,"",((MAX($O$2:$O$10000)-$O2)*500000)+$N2)

 

[DRSteele]

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]

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]

Are these the expected results?

Pasta1
	H	I	J	K	L	M	N	O	P	Q	R
1	Salesperson					sales stage	sales revenue	Start Date	End Date	Next Step Date	Rank2
2	Thomas					Lost	10000000	3/1/2022	2/29/2024	1/31/2022	2
3	Jennifer					Lost	14000000	3/1/2022	9/30/2024	1/31/2022	1
4	George					Lost	9310680	3/5/2022	3/31/2024	2/4/2022	3
5	Kyle					Lost	14700000	4/1/2022	2/28/2024	3/9/2022	4
6	Jack					Lost	5610360	5/1/2022	4/30/2025	4/5/2022	5
7	Jake					Lost	5342000	8/1/2022	7/31/2024	7/14/2022	6
8	Reece					Lost	19515480	9/1/2022	8/31/2024	7/31/2022	8
9	Mason					Lost	29000000	9/1/2022	8/31/2025	8/10/2022	7
10	Harry					Open	10000000	2/1/2022	1/31/2024	1/25/2022	1
11	Michele					Open	20000000	3/1/2022	2/29/2024	1/31/2022	2
12	Owen					Open	35000000	4/1/2022	3/31/2024	3/31/2022	4
13	Oscar					Open	47407457	4/1/2022	3/31/2024	2/28/2022	3
14	David					Open	16000000	4/6/2022	4/5/2025	3/22/2022	6
15	Callum					Open	42000000	4/6/2022	4/5/2025	3/17/2022	5
16	Joe					Open	9900000	5/1/2022	4/30/2024	4/4/2022	8
17	Rhys					Open	24092040	5/1/2022	4/30/2024	4/5/2022	7
18	Charlie					Open	37400000	5/21/2022	5/20/2025	4/4/2022	9
19	Damian					Open	8000000	7/1/2022	6/30/2024	6/17/2022	10
20	Liam					Open	34880000	7/20/2022	7/19/2025	5/31/2022	11
21	William					Open	12000000	8/1/2022	7/31/2025	7/13/2022	12
22	Ethan					Open	7532760	8/18/2022	8/17/2024	7/8/2022	13
23	Alexander					Open	7000000	9/1/2022	8/31/2024	5/31/2022	15
24	James					Open	26429400	9/1/2022	8/31/2025	8/17/2022	14
25	Daniel					Open	24000000	10/1/2022	9/30/2024	9/14/2022	17
26	Dennis					Open	39800000	10/1/2022	9/30/2024	9/21/2022	16
27	Connor					Won	10759416	10/1/2022	9/30/2025	9/15/2022	1
28	Jacob					Won	32151240	11/1/2022	10/31/2025	10/5/2022	2
29	Michael					Won	36784160	12/1/2022	11/30/2025	10/31/2022	3
30	Charles					Won	7783080	1/1/2023	12/31/2025	12/22/2022	4
31	Jackie					Won	25000000	2/1/2023	1/31/2025	1/1/2023	5
32
Plan1

Cell Formulas
Range	Formula
R2:R31	R2	=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]

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!