Rank using countifs and avoiding duplicates

trying to adjust this formula "IF(AE17=1,COUNTIFS($AE$14:$AE$171,1,$AD$14:$AD$171,">"&AD17)+1,"")" so that i can avoid duplicate ranks, like 81 in the attached when there are ties. Using this formula to do the rank as i am only ranking certain members of the list. This formula is in column A of Sheet1 on the attached. A simplified end result of what in need final product to be is shown on Sheet2. The data is an active OLAP pivot table, so i can not alter its output. i have tried using various formulas in a helper column, but have not been successful. the desire for a tie would be to put them ties in order of rows from lowest to highest.
Thanks for any assistance.