• 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

    Hui...

  • 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

Nishant859_1

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).
 

Attachments

vletm

Excel Ninja
Nishant859_1
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";""})
 

Attachments

Top