Excel Rank with MANY conditions skip ranks. Please help. my head is spinning

[teddybear2018]

This formula below was working
=IF(AP22="","N/A",IF(AP22="0","N/A",SUMPRODUCT(--($D$4:$D$387=D22),--($AP$4:$AP$387<>"")*(AP22<$AP$4:$AP$387)/COUNTIF($AP$4:$AP$387,$AP$4:$AP$387&""))+1))

NOW I had to add an additional condition, now it skips ranks. I no longer have rank 1 or 3,4. it just skips numbers

=IF(G36="Tech","N/A",IF(AP36="","N/A",IF(AP36="0","N/A",SUMPRODUCT(--($D$4:$D$387=D36),--($AP$4:$AP$387<>"")*(AP36<$AP$4:$AP$387)/COUNTIF($AP$4:$AP$387,$AP$4:$AP$387&""))+1)))

I just wanted to exclude tech dept.
If G36 is tech, or AP=36 is blank or 0 come back as "N/A", but the rest please rank. and NOT skip numbers

 

[jasonb75]

It is possible, but the example that I asked for in post 2 is still needed.

I did edit post 8 with a slight change to the formula but I don't think that it is going to work any better.

I suggest that you go back to the top of the thread and read every reply carefully, I seem to be constantly repeating myself or directing you back to earlier replies because you haven't read them properly.