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

**grayjean** · 02-10-2017, 10:38 AM

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.

**CAntosh** · 02-10-2017, 12:34 PM

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: