Rankif

[sobrien1234]

Is it possible to do a rankif formula similar to an averageif?
The average if formula would be = averageif(A$1:A$100,b1,c$1:c$1000) and so on so I could copy it down column D

 

[jasonb75]

You can use countifs to rank with a <B1 or >B1 criteria depending on which way you want to rank.

 

[Peter_SSs]

Could you post a small set of sample data and the expected results with XL2BB?

 

[sobrien1234]

Example.xlsx
	E
7
Sheet1

So the answer in column C ranks the times in column B but only for the same code in column A - hope that makes sense? need a formula for column C that I can copy down as have thousands of rows of data

 

[Peter_SSs]

You need to select the range that you want displayed before clicking 'Capture Range'

 

[sobrien1234]

Sorry first time - try this:

Example.xlsx
	A	B	C
1	Code	Time	Answer
2	WAN439789	18.12	3
3	WAN439789	18.04	1
4	WAN439789	18.42	7
5	WAN439789	18.46	8
6	WAN439789	18.26	4
7	WAN439789	18.11	2
8	WAN439789	18.32	6
9	WAN439789	18.30	5
10	WAN4397810	31.73	6
11	WAN4397810	31.55	5
12	WAN4397810	31.22	4
13	WAN4397810	31.08	2
14	WAN4397810	31.02	1
15	WAN4397810	31.12	3
16	WAN4397810	32.57	7
17	WAN4397810	32.72	8
18	WAN4397811	17.90	1
19	WAN4397811	18.18	5
20	WAN4397811	18.44	8
21	WAN4397811	18.35	7
22	WAN4397811	18.10	2
23	WAN4397811	18.16	4
24	WAN4397811	18.32	6
25	WAN4397811	18.15	3
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=RANK(B2,B$2:B$9,1)
C10:C17	C10	=RANK(B10,B$10:B$17,1)
C18:C25	C18	=RANK(B18,B$18:B$25,1)

 

[jasonb75]

As I pointed out earlier

You can use countifs to rank with a <B1 or >B1 criteria depending on which way you want to rank.

This will give the same results as the RANK formulas in your example

=1+COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,"<"&B2)

 

[Peter_SSs]

SUMPRODUCT is also an option

20 05 27.xlsm
	A	B	C
1	Code	Time	Answer
2	WAN439789	18.12	3
3	WAN439789	18.04	1
4	WAN439789	18.42	7
5	WAN439789	18.46	8
6	WAN439789	18.26	4
7	WAN439789	18.11	2
8	WAN439789	18.32	6
9	WAN439789	18.3	5
10	WAN4397810	31.73	6
11	WAN4397810	31.55	5
12	WAN4397810	31.22	4
13	WAN4397810	31.08	2
14	WAN4397810	31.02	1
15	WAN4397810	31.12	3
16	WAN4397810	32.57	7
17	WAN4397810	32.72	8
18	WAN4397811	17.9	1
19	WAN4397811	18.18	5
20	WAN4397811	18.44	8
21	WAN4397811	18.35	7
22	WAN4397811	18.1	2
23	WAN4397811	18.16	4
24	WAN4397811	18.32	6
25	WAN4397811	18.15	3
Rank in groups

Cell Formulas
Range	Formula
C2:C25	C2	=SUMPRODUCT(($A$2:$A$1000=A2)*(B2>$B$2:$B$1000))+1

 

[sobrien1234]

As I pointed out earlier

This will give the same results as the RANK formulas in your example

=1+COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,"<"&B2)

Amazing!! I could kiss you!! Thanks