Rank values between two dates

[TheJonWithNoH]

Hello,

I am trying to rank values from largest to smallest between two sets of dates but running into some issues. The dates are in column B and I am trying to rank the numbers in column H and the ranking is in Column J.

This is the formula that I am currently using:

=IFERROR(IF(MONTH(B2)=MONTH(Setup!$H$14),(RANK.EQ(H2,$H$2:$H$10000,0)+COUNTIF(H2:H$10000,H2)-1),""),"")

The only issue that I am running into here is for example if I am trying to rank the values for December, some ranking will not show up because the values are in another month. How can I get the "Value if False" to show the next ranking?

Hope I am making sense here.

Thank you in advance for help!

JB

 

[DRSteele]

I fear I might not be answering the question you asked, but I think this might help. Since there is no function RANKIFS (and there should be!), we can use function SUMPRODUCT to rank with conditions. This video from six years ago even laments the lack of the function. https://www.youtube.com/watch?v=rCeTG2k2T6I

	A	B	C	D
start	end
Date	Value	Rank

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/5/2017[/TD]
[TD="align: right"]11/14/2017[/TD]

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

[TD="align: center"]4[/TD]

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

[TD="align: center"]5[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]11/3/2017[/TD]
[TD="align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]11/4/2017[/TD]
[TD="align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]11/5/2017[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]11/6/2017[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]11/7/2017[/TD]
[TD="align: right"]500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11/8/2017[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]11/9/2017[/TD]
[TD="align: right"]500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]11/10/2017[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]11/11/2017[/TD]
[TD="align: right"]60[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]11/12/2017[/TD]
[TD="align: right"]50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]11/13/2017[/TD]
[TD="align: right"]40[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]11/14/2017[/TD]
[TD="align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]11/15/2017[/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]11/16/2017[/TD]
[TD="align: right"]900[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]11/17/2017[/TD]
[TD="align: right"]1000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="align: right"][/TD]

</tbody>

rankif2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=IF(OR(A5<$C$2,A5>$D$2),"",SUMPRODUCT(($A$5:$A$21>=$C$2)*($A$5:$A$21<=$D$2)*(B5<$B$5:$B$21))+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]