• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find TOP scorer with Conditions


New Member
Dear Experts,

Requirement is to
1) get top 20 score
2) & top 21 to 40 Scorer
(based on Region, where Contract Status is not equal to ML6 & Disqualified

Sample Data attached for refence (Column E is just for a reference not a actual mapping).



Excel Ninja
Your sample file has 40 rows of data.
Which of Your required results shows Your 1) as well as Your 2) ... based as You've written in #1Reply?
With only 40 records, all rank is under 40.

Maybe try

=LOOKUP(RANK.AVG(D2,$D$2:$D$99),{1;21;41},{"Top 20 Scorer";"Top 21 to 40 Scorer";""})

or by region
=LOOKUP(COUNTIFS($B$2:$B$99,B2,$D$2:$D$99,">="&D2),{1;21;41},{"Top 20 Scorer";"Top 21 to 40 Scorer";""})