Ranking values (in descending order) with 1 criteria - BUT without skipping numbers

ellison

Active Member
Joined
Aug 1, 2012
Messages
356
Office Version
  1. 365
Platform
  1. Windows
Hi, we are trying to put ranks on some info and are "most" of the way there with a formula that we are using: =COUNTIFS(A:A,A2,B:B,">"&B2)+1

So we are able to: Rank the values in descending order, based on the criteria (=location number from our data).

BUT when there are duplicate values, we would like this version of a ranking to not "skip numbers"

In other words on the example, for location-02 on rows 3 to 5, there are 2 subtotals which are the highest amount (20,000), but we would like the next highest ranking to be "2" (as opposed to what the formula shows which is "3")

By the way, we can see why the formula would show 3 (because there are 2 values with the highest amount)

Is this possible at all?!

...showing some sample info to hopefully show this a little bit clearer!



rank-values-in-descending-order-with-criteria-&-no-skipping-numbers-01-question.xlsx
ABCD
1DepartmentSubtotalRank in Descending OrderThe version of rank that we would like it to have (if different from what is being shown)
2Location-0115,0001
3Location-0220,0001
4Location-0220,0001
5Location-029,0003To rank "2" as it's the second highest amout for location-02
6Location-0312,0001
7Location-0312,0001
8Location-0312,0001
9Location-0312,0001
10Location-0312,0001
11Location-039,0006To rank "2" as it's the second highest amout for location-03
12Location-035,00011… to rank 3 as it's the 3rd highest amount for location-03
13Location-035,00011… to rank 3 as it's the 3rd highest amount for location-03
14Location-035,00011… to rank 3 as it's the 3rd highest amount for location-03
15Location-035,00011… to rank 3 as it's the 3rd highest amount for location-03
16Location-035,00011… to rank 3 as it's the 3rd highest amount for location-03
17Location-039,0006To rank "2" as it's the second highest amout for location-03
18Location-039,0006To rank "2" as it's the second highest amout for location-03
19Location-039,0006To rank "2" as it's the second highest amout for location-03
20Location-039,0006To rank "2" as it's the second highest amout for location-03
Sheet1
Cell Formulas
RangeFormula
C2:C20C2=COUNTIFS(A:A,A2,B:B,">"&B2)+1


rank-values-in-descending-order-with-criteria-&-no-skipping-numbers-01-question
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
How about
Fluff.xlsm
ABCD
1DepartmentSubtotalRank in Descending Order
2Location-011500011
3Location-022000011
4Location-022000011
5Location-02900032
6Location-031200011
7Location-031200011
8Location-031200011
9Location-031200011
10Location-031200011
11Location-03900062
12Location-035000113
13Location-035000113
14Location-035000113
15Location-035000113
16Location-035000113
17Location-03900062
18Location-03900062
19Location-03900062
20Location-03900062
Data
Cell Formulas
RangeFormula
C2:C20C2=COUNTIFS(A:A,A2,B:B,">"&B2)+1
D2:D20D2=XMATCH(B2,SORT(UNIQUE(FILTER($B$2:$B$20,$A$2:$A$20=A2)),,-1))
 
Upvote 0
Solution
Wow, that is FANTASTIC!!!

many, many thanks - marked up as the solution 💪 💪 💪 💪
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

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
Back
Top