Rank values between two dates

TheJonWithNoH

New Member
Joined
Sep 8, 2017
Messages
30
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,330
Office Version
  1. 365
Platform
  1. Windows
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

ABCD
1startend
211/5/201711/14/2017
3
4DateValueRank
511/1/201720
611/2/201725
711/3/20178
811/4/201716
911/5/20171003
1011/6/201729
1111/7/20175001
1211/8/201738
1311/9/20175001
1411/10/2017110
1511/11/2017604
1611/12/2017505
1711/13/2017406
1811/14/2017307
1911/15/20175
2011/16/2017900
2111/17/20171000

<tbody>
</tbody>
rankif2

Worksheet Formulas
CellFormula
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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?

Disable AdBlock

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
Top