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]

Maybe

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

Such formulas always get messy once you start trying to add bits to them.

I suspect that this could return #DIV/0! errors, but without sample data and expected results to test on this is about as good as it gets.

Please post any data samples using XL2BB (Not screen captures), remember to fictionalise any personal / confidential details before posting and type in the expected results for the sample so that we know what we are aiming for.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[teddybear2018]

Thank you for the suggestion. I want to this but I guess its not complete formula
I can't close the formula to try. Its not the extra ")" at the end. What could I be missing?

 

[jasonb75]

The formula closes fine for me, maybe you didn't copy it correctly?

 

[teddybear2018]

It closes but now not tech is coming back #DIV/0! errors

 

[jasonb75]

I thought that might happen, see post 2 (the bit below the formula).

 

[teddybear2018]

I am sorry where is Post2?

 

[jasonb75]

Scroll up, it's the reply with the formula.

You could try changing the formula slightly, but I don't think that it will give the correct results.

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

But without the additional information that I asked for in post 2 your formula is too much of a mess to break down and make sense of.

 

[teddybear2018]

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

Are you referring to this? This isn't working for me...

 

[teddybear2018]

yea I guess it's too much... I will manually work around it. thanks so much for your help!