Rank results based on multiple variables

I am ranking (column AH)  the points for a group of members based on if they meet all percentages greater than or equal 100.  My problem is when the point total (column AG) for a non-qualified member is greater than the qualified member.  I cannot figure out how to exclude the non-qualified member from the formula.  I've attached my spreadsheet and my formula is:  

=IF(NOT($G2>=100%),"Not Qualified",IF(NOT($M2>=100%),"Not Qualified",IF(NOT($S2>=100%),"Not Qualified",RANK($AG2,$AG2:$AG4)+SUMPRODUCT(--($AG$2:$AG$4=$AG2),--($D$2:$D$4>$D2)))))
Ranking_EE_111417.xlsx
castlerjSenior AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Ignoring row 3 for a moment, please explain in english why row 4 ranks higher than row 2.
castlerjSenior AnalystAuthor Commented:
Because it has more points than row 2: 36 vs. 34 (column AG)
ShumsExcel & VBA ExpertCommented:
Hi,

Replace your formula with below for ranking:
=IF(OR($G2<100%,$M2<100%,$S2<100%),"Not Qualified",RANK($AG2,$AG$2:$AG$4)+SUMPRODUCT(--($AG$2:$AG$4=$AG2),--($D$2:$D$4>$D2)))

Open in new window

Add extra column to exclude "Not Qualified" with below formula:
=IFERROR(IF(ISNA(RANK($AH2,$AH$2:$AH$4)),"Not Qualified",RANK($AH2,$AH$2:$AH$4)),"")

Open in new window

Hope this helps.
Ranking_EE_111417.xlsx
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Learn More!
castlerjSenior AnalystAuthor Commented:
Thanks Shums, but the rank is off in column AI.  How do I reverse it?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Shouldn't it just be this?
=IF(NOT($G2>=100%),"Not Qualified",IF(OR(NOT($M2>=100%),NOT($S2>=100%)),"Not Qualified",SUMPRODUCT(--($M$2:$M$4>=100%),--($S$2:$S$4>=100%),--($AG$2:$AG$4>$AG2))+1))

Open in new window

castlerjSenior AnalystAuthor Commented:
Right on Subodh!  I did not consider adding column M & S.  What made you go down that path?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Because you wanted to rank with those criteria also. :)
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Also you can wrap all your Not Qualified conditions within OR criteria like below...

=IF(OR(NOT($G2>=100%),NOT($M2>=100%),NOT($S2>=100%)),"Not Qualified",SUMPRODUCT(--($M$2:$M$4>=100%),--($S$2:$S$4>=100%),--($AG$2:$AG$4>$AG2))+1)

Open in new window

castlerjSenior AnalystAuthor Commented:
Got it.  I played with it and came across an issue if there is a tie.  I would like to break the tie based on the point with the larger amount in column J.  Can you help?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe this Array Formula which will require confirmation with Ctrl+Shift+Enter instead of Enter alone.
=IF(OR(NOT($G2>=100%),NOT($M2>=100%),NOT($S2>=100%)),"Not Qualified",(IF(COUNTIF($AG$2:$AG$4,AG2)=1,SUM(($M$2:$M$4>=100%)*($S$2:$S$4>=100%)*($AG$2:$AG$4>$AG2))+1,SUM(($M$2:$M$4>=100%)*($S$2:$S$4>=100%)*($J$2:$J$4>$J2))+1)))

Open in new window

Martin LissOlder than dirtCommented:
If a VBA solution is OK then click the "Rank" button in AH1. Note that it takes ties into consideration.
29068367.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You can replace the previously suggested Array Formula with the following Array Formula.
Tested it in the Martin's file.

=IF(OR(NOT($G2>=100%),NOT($M2>=100%),NOT($S2>=100%)),"Not Qualified",(IF(COUNTIF($AG$2:$AG$5,AG2)=1,SUM(($M$2:$M$5>=100%)*($S$2:$S$5>=100%)*($AG$2:$AG$5>$AG2))+1,SUM(($M$2:$M$5>=100%)*($S$2:$S$5>=100%)*($J$2:$J$5>$J2)))))

Open in new window

castlerjSenior AnalystAuthor Commented:
Thank you both!! I really liked the VBA usage and can use it in other spreadsheets.  Subodh, thank you for your efforts too and making me think differently.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome! Glad we could help. :)
Martin LissOlder than dirtCommented:
Please don't forget to close this question.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.