Rank Pivot Results WITHOUT DUPLICATES

Mike Meyer

Hey friends, I am trying to create a column that ranks (without duplicates) the results of a PIVOT configuration.

The PIVOT config produces the SUM of rental dollars spent, in column "Grand Total-SUM 💲 Total Rental $ Amount {H}" respective to each listed equipment type, in column "⚠️Cat-Class Description".

I'd like to rank the equipment types by most rental dollars spent. In results, some equipment types had the same rental dollars spent and therefore produced duplicate rank #'s. This makes it difficult while on a separate helper / metric rollup sheet I use cross-sheet references to show only the top 25 equipment types. So, I want to be able to rank (without duplicates). NOTE: the complication is - this is a YTD sheet so now it only has 13 rows, but by the end of the year it will have over 1000 rows. The rankings change frequently, so using this pivot sheet as the source data for a dashboard causes problems, hence the need for the helper / metric rollup sheet that uses index match to only show the Top 25 equipment types. This works great (when the rankings don't have duplicates).

I searched the community forum for help and found formulas that work, but honestly, I don't really understand WHY 🤷‍♂️. Can someone please explain to me WHY these work? I supplied the screenshots. I don't understand why screenshot 2 & 3 have to have the "-2" at the end to work; I imagine it has to do with accounting for the "Grand (Total)" row….

Also, on a different sheet, the PIVOT results only list one row outside the "Grand (Total)" row and in that sheet these ranking formulas produce "0" instead of "1" in that row…I imagine because the amount for Row 1 is the same amount as the "Grand (Total)" row… But I don't know how to fix it...

Thank you in advance for your help!

Jason Tarpinian

The COUNTIFS is counting up which iteration of that ranking that particular row is (so if it is the first time 100 appears, the COUNTIFS portion would return 1, if it is the second time 100 appears the COUNTIFS would return 2, all prioritized on the "Row ID" value). So if it was rank 1, and the first iteration, the value returned of your RANKEQ + COUNTIFS is 2, so you would subtract 1 to get the true ranking. But I can't understand why you would subtract 2, versus 1, unless you want to have a ranking of 0 somewhere? Maybe that's what you mean in your last paragraph, where rank 0 is the grand total.

Jason Tarpinian

The COUNTIFS is counting up which iteration of that ranking that particular row is (so if it is the first time 100 appears, the COUNTIFS portion would return 1, if it is the second time 100 appears the COUNTIFS would return 2, all prioritized on the "Row ID" value). So if it was rank 1, and the first iteration, the value returned of your RANKEQ + COUNTIFS is 2, so you would subtract 1 to get the true ranking. But I can't understand why you would subtract 2, versus 1, unless you want to have a ranking of 0 somewhere? Maybe that's what you mean in your last paragraph, where rank 0 is the grand total.

Mike Meyer

Thank you! @jasontarpinian