Ranking with exclusions

[andrewc]

Hi,

I have a list of names in column A and their associated values in column B.

I would like to rank in column C the values (in column B) of each of the items in column A that aren’t “Apple”; values in column B associated with the word “Apple” should be excluded from the ranking calculation.

Can someone please suggest a solution?

Thanks!

[newdoverman]

Maybe this would work for you.

	A	B	C	D
1	ITEM	VALUE	HELPER	RANK
2	Pears	1	1	7
3	bears	2	2	6
4	apples	3
5	oranges	4	4	4
6	peaches	5	5	3
7	apples	6
8	bananas	14	14	1
9	grapes	3	3	5
10	bananas	11	11	2
11
12
13	HELPER
14	=IF(A2="apples","",B2)
15
16	RANK
17	=IFERROR(RANK(C2,$C$2:$C$10,0),"")

[MarvinP]

Hi Andrew,

My answer may be the same as Newdoverman, but see if it is. See the attached.

[daddylonglegs]

Assuming the highest value should be ranked 1 you can do that without any helper cells if you use this formula in C2 copied down

=IF(A2="apple","",COUNTIFS(A:A,"<>apple",B:B,">"&B2)+1)

If you want to rank the lowest value as 1 then just change the ">" to a "<"