+ Reply to Thread
Results 1 to 2 of 2

How to use RANK or similar function IF multiple criteria are true? Sorta like a RANKIF?

  1. #1
    Registered User
    Join Date
    02-09-2017
    Location
    Tennessee
    MS-Off Ver
    2010, 2016
    Posts
    1

    How to use RANK or similar function IF multiple criteria are true? Sorta like a RANKIF?

    I am trying to rank a list of sales reps, using the results (columns E and H). I can easily use RANK to rank the entire organization from best performing to worst. But I don't need to do that. I need to be able to rank each sales rep only in each of the three categories they fit into. I know there is not a specific RANKIF or RANKIFS function, but I need to do something that is essentially that. For example, Rank E2:E13 but only IF the cell in column B equals "Veteran" and E2:E13 does not contain any Xs.

    Here's the purpose for this: I work for a sales organization and I need to be able to rank all reps by their performances. Then I need to be able to identify which of them are in the bottom 50% of their group for two consecutive months. Also, not all reps will be factored into the equation...and that's another IF I suppose. They are only counted if they have a goal set for the month (i.e., they are on a quota plan; if they are not an X is in the "goal columns". Here is just an example of something like what I have to work with:

    SALES REP LEVEL MONTH 1 GOAL MONTH 1 SALES MONTH 1 PERFORMANCE MONTH 2 GOAL MONTH 2 SALES MONTH 2 PERFORMANCE
    ADAM Veteran X X X 25,000 18,000 72%
    BART Veteran X X X 25,000 18,000 64%
    JAMES Veteran 25,000 20,000 80% 25,000 22,500 90%
    PETER Veteran 25,000 22,000 88% 25,000 25,000 100%
    ANDREW Senior 15,000 14,000 93% 15,000 10,250 68%
    JOHN Senior 15,000 14,000 93% 15,000 11,400 76%
    JUDE Senior 15,000 11,000 73% 15,000 12,200 81%
    NATHAN Senior 15,000 13,000 87% 15,000 14,700 98%
    MATTHEW Junior X X X 7,500 10,000 133%
    PHILLIP Junior X X X 7,500 7,100 95%
    SIMON Junior 7,500 5,000 67% 7,500 2,500 33%
    THOMAS Junior 7,500 4,500 60% 7,500 1,000 13%

    An "X" indicates the person was not on a quota plan for the month, so they don't get counted. So instead of finding the bottom 2 people in the group of four (that's 50%), I'm just finding the bottom person (bottom 50%; because there's only 2 people in the group who are competing now).

    The reps in BOLD are those who were in the bottom 50% for the one month. In order to meet the criteria however, they must be in the bottom 50% for two consecutive months. As you can see above, Thomas is the only person who meets this criteria. (This is just for your reference; I don't need any formula that formats these numbers or whatnot; just a function to the left of the table that will just give me an indicator).

    Basically, I want to have a column before this table, at the beginning, that checks to see if a rep meets BOTH criteria. If they do, the formula will return "Needs Improvement" and if they do not, the formula should return "OK").

    Any help would be lifesaving. I'm driving myself insane over this you guys.

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,105

    Re: How to use RANK or similar function IF multiple criteria are true? Sorta like a RANKIF

    Given the manner in which you're trying to both calculate a ranking in multiple columns AND immediately use that ranking in additional calculations, it would make sense to use helper columns to make the process more manageable. I would advise inserting a column after Month 1 Performance (a new column F) and Month 2 Performance (New J) to calculate each rep.'s monthly status. You could then use these helper columns in Conditional Formatting or a master column to achieve your results. For the new column F, you could use the formula below in F2, then fill down:

    =IF(E2="X","X",(IF((SUMPRODUCT(--($B$2:$B$13=$B2),--(E$2:E$13<>"x"),--(E2<E$2:E$13))+1)/COUNTIFS($B$2:$B$13,$B2,E$2:E$13,"<>"&"X")>0.5,"Bottom Half","Top Half")))

    This will produce an "X" in non-qualifiers and a "Top Half" or "Bottom Half" in qualifiers. You can paste the formula over to the new column J. Repeat for any other months. Once everything is in place, you can hide the helper columns if you don't want to see them. The attachment has a sample helper column set up in column F. I also left the formula for rank in column I; you don't need it, but I figured it might be easier to dissect if it's isolated. Take a look to see if it does the trick:
    Attached Files Attached Files
    Last edited by CAntosh; 02-10-2017 at 12:39 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Rankif function - Is there a way to rank based on an argument.
    By JessieMTX in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-19-2016, 02:34 PM
  2. [SOLVED] Creating a function which handles multiple true criteria
    By ddman3 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-30-2015, 09:37 PM
  3. Rank function with multiple criteria
    By chr15williams in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-29-2015, 02:21 PM
  4. [SOLVED] Rankif 3 criteria with tie breaker
    By huy_le in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-24-2014, 01:13 PM
  5. Rankif with 2 criteria
    By Jonathan9 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-03-2014, 05:21 AM
  6. [SOLVED] Rankif with 2 criteria
    By huy_le in forum Excel Formulas & Functions
    Replies: 40
    Last Post: 04-15-2014, 03:33 PM
  7. Is there a rankif function
    By Dan in forum Excel General
    Replies: 3
    Last Post: 05-27-2005, 01:05 PM

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