It would be awesome if RANKIF existed. But it doesn't. So I need help!

[Painzstake]

I have been working on this ranking problem for a while now. I've tried multiple combinations of COUNTIF and SUMPRODUCT, but I can't seem to get my formula to work! I thought I'd jump back into these forums to see if anybody was up for the challenge?

Problem: column C needs to contain dates ranked from column A. These dates are to be ranked by the groups in Column B in the following manner; if B is "Old", then rank as descending whole values; if B is "New", then rank as ascending decimal values; if B is "Posted", then leave blank.

For context: the final formula is going to be arrayed from C2.

This table shows the desired result:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Date
[/TD]
[TD]Is
[/TD]
[TD]Order
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

10/02/2018 16:41:00​

[/TD]
[TD]Old
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

14/02/2018 18:02:00​

[/TD]
[TD]Posted
[/TD]
[TD]""
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

17/02/2018 21:59:00​

[/TD]
[TD]Posted
[/TD]
[TD]""
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

17/02/2018 22:40:00​

[/TD]
[TD]Old
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

21/02/2018 22:49:00​

[/TD]
[TD]Posted
[/TD]
[TD]""
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

22/02/2018 22:11:00​

[/TD]
[TD]Old
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

24/02/2018 10:36:00​

[/TD]
[TD]New
[/TD]
[TD].1
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

17/02/2018 20:36:00​

[/TD]
[TD]New
[/TD]
[TD].2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Worf]

I used a table:

 

[wideboydixon]

Alternatively:

Book1
	A	B	C	D
1	Date	Is	Order	Order
2	10/02/2018 16:41	Old	3	3
3	14/02/2018 18:02	Posted
4	17/02/2018 21:59	Posted
5	17/02/2018 22:40	Old	2	2
6	21/02/2018 22:49	Posted
7	22/02/2018 22:11	Old	1	1
8	24/02/2018 10:36	New	0.2	0.1
9	17/02/2018 20:36	New	0.1	0.2
Sheet1

Cell Formulas
Range	Formula
C2	=IF($B2="Old",COUNTIFS($B$2:$B$9,"Old",$A$2:$A$9,">"&$A2)+1,IF($B2="New",(COUNTIFS($B$2:$B$9,"New",$A$2:$A$9,"<"&$A2)+1)/10,""))
D2	=IF($B2="Old",COUNTIFS($B$2:$B$9,"Old",$A$2:$A$9,">"&$A2)+1,IF($B2="New",(COUNTIFS($B$2:$B$9,"New",$A$2:$A$9,">"&$A2)+1)/10,""))

Ranking "New" values in ascending order should give the results as posted in Column C, not as your example. If you want this the other way then take the formula from column D.

WBD

 

[Painzstake]

Alternatively:

	A	B	C	D
Date	Is	Order	Order
Old
Posted
Posted
Old
Posted
Old
New
New

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10/02/2018 16:41[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]14/02/2018 18:02[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]17/02/2018 21:59[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]17/02/2018 22:40[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]21/02/2018 22:49[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]22/02/2018 22:11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]24/02/2018 10:36[/TD]

[TD="align: right"]0.2[/TD]
[TD="align: right"]0.1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]17/02/2018 20:36[/TD]

[TD="align: right"]0.1[/TD]
[TD="align: right"]0.2[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF($B2="Old",COUNTIFS($B$2:$B$9,"Old",$A$2:$A$9,">"&$A2)+1,IF($B2="New",(COUNTIFS($B$2:$B$9,"New",$A$2:$A$9,"<"&$A2)+1)/10,""))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF($B2="Old",COUNTIFS($B$2:$B$9,"Old",$A$2:$A$9,">"&$A2)+1,IF($B2="New",(COUNTIFS($B$2:$B$9,"New",$A$2:$A$9,">"&$A2)+1)/10,""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Ranking "New" values in ascending order should give the results as posted in Column C, not as your example. If you want this the other way then take the formula from column D.

WBD

Wideboydixon, this is exactly what I was looking for. I appreciate you taking the time to help me out with this. It's being doing my head in for the past few days. The formula works great!

Special thanks to Worf for his contribution too! I'm amazed people like you guys take the time to help strangers with their excel problems.

All the best,


Painzstake