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
1			start	end
2			11/5/2017	11/14/2017
3
4	Date	Value	Rank
5	11/1/2017	20
6	11/2/2017	25
7	11/3/2017	8
8	11/4/2017	16
9	11/5/2017	100	3
10	11/6/2017	2	9
11	11/7/2017	500	1
12	11/8/2017	3	8
13	11/9/2017	500	1
14	11/10/2017	1	10
15	11/11/2017	60	4
16	11/12/2017	50	5
17	11/13/2017	40	6
18	11/14/2017	30	7
19	11/15/2017	5
20	11/16/2017	900
21	11/17/2017	1000

<tbody>
</tbody>

rankif2​

Worksheet Formulas Cell Formula C5 =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) <tbody> </tbody>

<tbody>
</tbody>