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
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 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Department | Subtotal | Rank in Descending Order | The version of rank that we would like it to have (if different from what is being shown) | ||
| 2 | Location-01 | 15,000 | 1 | |||
| 3 | Location-02 | 20,000 | 1 | |||
| 4 | Location-02 | 20,000 | 1 | |||
| 5 | Location-02 | 9,000 | 3 | To rank "2" as it's the second highest amout for location-02 | ||
| 6 | Location-03 | 12,000 | 1 | |||
| 7 | Location-03 | 12,000 | 1 | |||
| 8 | Location-03 | 12,000 | 1 | |||
| 9 | Location-03 | 12,000 | 1 | |||
| 10 | Location-03 | 12,000 | 1 | |||
| 11 | Location-03 | 9,000 | 6 | To rank "2" as it's the second highest amout for location-03 | ||
| 12 | Location-03 | 5,000 | 11 | … to rank 3 as it's the 3rd highest amount for location-03 | ||
| 13 | Location-03 | 5,000 | 11 | … to rank 3 as it's the 3rd highest amount for location-03 | ||
| 14 | Location-03 | 5,000 | 11 | … to rank 3 as it's the 3rd highest amount for location-03 | ||
| 15 | Location-03 | 5,000 | 11 | … to rank 3 as it's the 3rd highest amount for location-03 | ||
| 16 | Location-03 | 5,000 | 11 | … to rank 3 as it's the 3rd highest amount for location-03 | ||
| 17 | Location-03 | 9,000 | 6 | To rank "2" as it's the second highest amout for location-03 | ||
| 18 | Location-03 | 9,000 | 6 | To rank "2" as it's the second highest amout for location-03 | ||
| 19 | Location-03 | 9,000 | 6 | To rank "2" as it's the second highest amout for location-03 | ||
| 20 | Location-03 | 9,000 | 6 | To rank "2" as it's the second highest amout for location-03 | ||
Sheet1 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C20 | C2 | =COUNTIFS(A:A,A2,B:B,">"&B2)+1 |
rank-values-in-descending-order-with-criteria-&-no-skipping-numbers-01-question
