+ Reply to Thread
Results 1 to 3 of 3

Rank using Criteria

  1. #1
    Forum Contributor
    Join Date
    02-13-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2015
    Posts
    183

    Rank using Criteria

    I am building a weight loss tracker for a contest. I want to rank both the Male team and Female team by total weight lost each week.

    What I need to know how to do is in Column U I have a rank formula. What I need it to do is look at columns C and D and determine if the person is male or female and run two different ranks.

    For Example if there are 10 people 5 male and 5 female, the rank formula in column U needs to rank just males (column C) 1-5 and then just females (column D) 1-5.

    The spreadsheet is attached for reference.
    Attached Files Attached Files
    Last edited by sinspawn56; 03-10-2011 at 02:59 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Rank using Criteria

    In U3 enter this array formula:

    =IF(C3<>"", MATCH(T3, LARGE(IF($C$2:$C$101=C3,$T$2:$T$101),ROW(INDIRECT("1:"&COUNTIF($C$2:$C$101,C3)))),0), IF(D3<>"", MATCH(T3, LARGE(IF($D$2:$D$101=D3,$T$2:$T$101),ROW(INDIRECT("1:"&COUNTIF($D$2:$D$101,D3)))),0), ""))

    ...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. Then copy that cell down through U101.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    02-13-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2015
    Posts
    183

    Re: Rank using Criteria

    Work like a charm. kudos!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1