RANK on volume (Column B) if Date (Column D) is over a year old

[surkdidat]

I need to create "League Table" of anything over a year old, but it needs to be sorted by the largest volume. I have tried to use RANK, but when I reference it to Cell B which contains the volume, it does not want to work!

=IF(D2>TODAY()-365,"",RANK(B2,$B$2:$B$21,1))

 

[Phuoc]

Try

=IF(D2>TODAY()-365,"",COUNTIFS($D$2:$D$21,"<="&(TODAY()-365),$B$2:$B$21,"<"&B2)+1)

 

[surkdidat]

Hi
Unfortunately, that does not quite do what I need. It needs to rank the highest value in Column B with a 1, the second highest as a 2, etc, hence why I was looking at the RANK option....

Try

=IF(D2>TODAY()-365,"",COUNTIFS($D$2:$D$21,"<="&(TODAY()-365),$B$2:$B$21,"<"&B2)+1)

 

[Phuoc]

It needs to rank the highest value in Column B with a 1,
the second highest as a 2, etc,
hence why I was looking at the RANK option....

Change "<" to ">"

=IF(D2>TODAY()-365,"",COUNTIFS($D$2:$D$21,"<="&(TODAY()-365),$B$2:$B$21,">"&B2)+1)

 

[surkdidat]

Thank you. Works perfectly.

Change "<" to ">"

=IF(D2>TODAY()-365,"",COUNTIFS($D$2:$D$21,"<="&(TODAY()-365),$B$2:$B$21,">"&B2)+1)