Rankif formula for multiple columns of data

[Obewan]

I have the following:
AY = a list of codes (i.e. unique identifiers)
BM:BR - columns of data

In BS I have a formula as follows which I can copy down that column (starts on row 7):
=COUNTIFS($AY$7:$AY$100000,$AY7,$BN$7:$BN$100000,">"&BN7)+1

This works and ranks all numbers in column BN according to correpsonding code in AY

What I want to do is extend this formula so it covers all data in columns BN:BR for the code - I changed the formula to
=COUNTIFS($AY$7:$AY$100000,$AY7,$BN$7:$BR$100000,">"&BN7)+1

but just get an error message #VALUE!

Any suggestions?

Thanks in advance

Seamus

 

[ravi2628]

Hi Seamus,

Please find the logic.

Book1
	A	B	C	D	E	F
1	Name	Value 1	Value 2	Value 3	Value 4	Rank
2	A	19899	2548	6924	16092	1
3	A	16368	7110	2602	3109	2
4	A	4539	13118	12850	7343	9
5	B	9275	4583	19218	17005	6
6	B	7552	6088	2646	6701	7
7	B	5342	12409	13931	13988	10
8	C	16849	15661	15342	4995	1
9	C	14622	9506	1485	8484	4
10	C	3303	10856	10270	8276	11
11	D	1668	461	13779	12672	3
Sheet1

Cell Formulas
Range	Formula
F2:F11	F2	=1+SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$E$11>B2))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$3:$F$13	F2:F11

 

[Obewan]

Hi Seamus,

Please find the logic.

Book1
	A	B	C	D	E	F
1	Name	Value 1	Value 2	Value 3	Value 4	Rank
2	A	19899	2548	6924	16092	1
3	A	16368	7110	2602	3109	2
4	A	4539	13118	12850	7343	9
5	B	9275	4583	19218	17005	6
6	B	7552	6088	2646	6701	7
7	B	5342	12409	13931	13988	10
8	C	16849	15661	15342	4995	1
9	C	14622	9506	1485	8484	4
10	C	3303	10856	10270	8276	11
11	D	1668	461	13779	12672	3
Sheet1

Cell Formulas
Range	Formula
F2:F11	F2	=1+SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$E$11>B2))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$3:$F$13	F2:F11

That works great with the exception it's picking up blank cells and ranking them all as 1. If I could exclude blanks cells would be perfect?

 

[ravi2628]

That works great with the exception it's picking up blank cells and ranking them all as 1. If I could exclude blanks cells would be perfect?

Can you share a sample of lines so that I can check the logic and share it with you?

 

[Obewan]

Can you share a sample of lines so that I can check the logic and share it with you?

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Code	1	1	1	1	1	Rank1	Rank2	Rank3	Rank4	Rank5
2	44216Ascot1	14.0	27.5	28.0	6.5	9.0	27	18	16	30	29
3	44216Ascot1	32.0	33.0	27.5	13.0	31.5	11	10	18	28	12
4	44216Ascot1	29.5	30.0	23.5	23.0		14	13	25	26	1
5	44216Ascot1	27.0	25.5	28.0			20	21	16	1	1
6	44216Ascot1	29.0	25.5	24.5			15	21	23	1	1
7	44216Ascot1	24.5					23	1	1	1	1
8	44216Ascot2	28.5	33.0	24.5	11.0	33.5	28	14	41	49	11
9	44216Ascot2	22.5	34.5	27.0	34.5	43.5	43	6	35	6	2
10	44216Ascot2	28.5	11.0	21.5	31.0	35.0	28	49	45	19	5
11	44216Ascot2	19.5	27.0	31.0	33.5	24.0	47	35	19	11	42
12	44216Ascot2	27.5	26.0	29.0	26.0	21.5	32	38	27	38	45
13	44216Ascot2	27.0	31.0	36.5	30.5	28.5	35	19	3	23	28
14	44216Ascot2	34.5	31.0	30.5	33.0	25.5	6	19	23	14	40
15	44216Ascot2	32.0	29.5	34.5	36.0	45.5	18	26	6	4	1
16	44216Ascot2	34.0	30.5	32.5	14.0	27.5	10	23	16	48	32
17	44216Ascot2	33.5	32.5	22.5	28.5	27.5	11	16	43	28	32
18	44216Ascot3	16.5	10.5	22.5	18.0	18.5	36	41	27	35	32
19	44216Ascot3	14.0	21.5	5.5	9.5	18.5	40	29	44	42	32
20	44216Ascot3	5.0	23.5	18.5	16.0	7.5	45	26	32	38	43
21	44216Ascot3	19.5	28.5	32.0			31	17	14	1	1
22	44216Ascot3	29.5	28.0	28.0	29.5	24.5	15	19	19	15	24
23	44216Ascot3	28.5	27.0	20.0			17	22	30	1	1
24	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
25	44216Ascot3	28.0	27.0	24.5			19	22	24	1	1
26	44216Ascot3	22.0	16.5				28	36	1	1	1
27	44216Ascot3	14.5					39	1	1	1	1
28	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
29	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
Sheet1

Cell Formulas
Range	Formula
G2:K29	G2	=1+SUMPRODUCT(($A$2:$A$99995=$A2)*($B$2:$F$99995>B2))

 

[ravi2628]

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Code	1	1	1	1	1	Rank1	Rank2	Rank3	Rank4	Rank5
2	44216Ascot1	14.0	27.5	28.0	6.5	9.0	27	18	16	30	29
3	44216Ascot1	32.0	33.0	27.5	13.0	31.5	11	10	18	28	12
4	44216Ascot1	29.5	30.0	23.5	23.0		14	13	25	26	1
5	44216Ascot1	27.0	25.5	28.0			20	21	16	1	1
6	44216Ascot1	29.0	25.5	24.5			15	21	23	1	1
7	44216Ascot1	24.5					23	1	1	1	1
8	44216Ascot2	28.5	33.0	24.5	11.0	33.5	28	14	41	49	11
9	44216Ascot2	22.5	34.5	27.0	34.5	43.5	43	6	35	6	2
10	44216Ascot2	28.5	11.0	21.5	31.0	35.0	28	49	45	19	5
11	44216Ascot2	19.5	27.0	31.0	33.5	24.0	47	35	19	11	42
12	44216Ascot2	27.5	26.0	29.0	26.0	21.5	32	38	27	38	45
13	44216Ascot2	27.0	31.0	36.5	30.5	28.5	35	19	3	23	28
14	44216Ascot2	34.5	31.0	30.5	33.0	25.5	6	19	23	14	40
15	44216Ascot2	32.0	29.5	34.5	36.0	45.5	18	26	6	4	1
16	44216Ascot2	34.0	30.5	32.5	14.0	27.5	10	23	16	48	32
17	44216Ascot2	33.5	32.5	22.5	28.5	27.5	11	16	43	28	32
18	44216Ascot3	16.5	10.5	22.5	18.0	18.5	36	41	27	35	32
19	44216Ascot3	14.0	21.5	5.5	9.5	18.5	40	29	44	42	32
20	44216Ascot3	5.0	23.5	18.5	16.0	7.5	45	26	32	38	43
21	44216Ascot3	19.5	28.5	32.0			31	17	14	1	1
22	44216Ascot3	29.5	28.0	28.0	29.5	24.5	15	19	19	15	24
23	44216Ascot3	28.5	27.0	20.0			17	22	30	1	1
24	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
25	44216Ascot3	28.0	27.0	24.5			19	22	24	1	1
26	44216Ascot3	22.0	16.5				28	36	1	1	1
27	44216Ascot3	14.5					39	1	1	1	1
28	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
29	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
Sheet1

Cell Formulas
Range	Formula
G2:K29	G2	=1+SUMPRODUCT(($A$2:$A$99995=$A2)*($B$2:$F$99995>B2))

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Code	1	1	1	1	1	Rank1	Rank2	Rank3	Rank4	Rank5
2	44216Ascot1	14	28	28	6.5	9	18	9	7	21	20
3	44216Ascot1	32	33	28	13	32	2	1	9	19	3
4	44216Ascot1	30	30	24	23		5	4	16	17
5	44216Ascot1	27	26	28			11	12	7
6	44216Ascot1	29	26	25			6	12	14
7	44216Ascot1	25					14
8	44216Ascot2	29	33	25	11	34	28	14	41	49	11
9	44216Ascot2	23	35	27	35	44	43	6	35	6	2
Sheet4

Cell Formulas
Range	Formula
G2:K9	G2	=IF(B2="","",1+SUMPRODUCT(($A$2:$A$99995=$A2)*($B$2:$F$99995>B2)))

 

[Obewan]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Code	1	1	1	1	1	Rank1	Rank2	Rank3	Rank4	Rank5
2	44216Ascot1	14	28	28	6.5	9	18	9	7	21	20
3	44216Ascot1	32	33	28	13	32	2	1	9	19	3
4	44216Ascot1	30	30	24	23		5	4	16	17
5	44216Ascot1	27	26	28			11	12	7
6	44216Ascot1	29	26	25			6	12	14
7	44216Ascot1	25					14
8	44216Ascot2	29	33	25	11	34	28	14	41	49	11
9	44216Ascot2	23	35	27	35	44	43	6	35	6	2
Sheet4

Cell Formulas
Range	Formula
G2:K9	G2	=IF(B2="","",1+SUMPRODUCT(($A$2:$A$99995=$A2)*($B$2:$F$99995>B2)))

Still not working for me?

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Code	1	1	1	1	1	Rank1	Rank2	Rank3	Rank4	Rank5
2	44216Ascot1	14.0	27.5	28.0	6.5	9.0	27	18	16	30	29
3	44216Ascot1	32.0	33.0	27.5	13.0	31.5	11	10	18	28	12
4	44216Ascot1	29.5	30.0	23.5	23.0		14	13	25	26
5	44216Ascot1	27.0	25.5	28.0			20	21	16
6	44216Ascot1	29.0	25.5	24.5			15	21	23
7	44216Ascot1	24.5					23
8	44216Ascot2	28.5	33.0	24.5	11.0	33.5	28	14	41	49	11
9	44216Ascot2	22.5	34.5	27.0	34.5	43.5	43	6	35	6	2
10	44216Ascot2	28.5	11.0	21.5	31.0	35.0	28	49	45	19	5
11	44216Ascot2	19.5	27.0	31.0	33.5	24.0	47	35	19	11	42
12	44216Ascot2	27.5	26.0	29.0	26.0	21.5	32	38	27	38	45
13	44216Ascot2	27.0	31.0	36.5	30.5	28.5	35	19	3	23	28
14	44216Ascot2	34.5	31.0	30.5	33.0	25.5	6	19	23	14	40
15	44216Ascot2	32.0	29.5	34.5	36.0	45.5	18	26	6	4	1
16	44216Ascot2	34.0	30.5	32.5	14.0	27.5	10	23	16	48	32
17	44216Ascot2	33.5	32.5	22.5	28.5	27.5	11	16	43	28	32
18	44216Ascot3	16.5	10.5	22.5	18.0	18.5	36	41	27	35	32
19	44216Ascot3	14.0	21.5	5.5	9.5	18.5	40	29	44	42	32
20	44216Ascot3	5.0	23.5	18.5	16.0	7.5	45	26	32	38	43
21	44216Ascot3	19.5	28.5	32.0			31	17	14
22	44216Ascot3	29.5	28.0	28.0	29.5	24.5	15	19	19	15	24
23	44216Ascot3	28.5	27.0	20.0			17	22	30
24	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
25	44216Ascot3	28.0	27.0	24.5			19	22	24
26	44216Ascot3	22.0	16.5				28	36
27	44216Ascot3	14.5					39
28	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
29	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
Sheet1

Cell Formulas
Range	Formula
G2:K29	G2	=IF(B2="","",1+SUMPRODUCT(($A$2:$A$99995=$A2)*($B$2:$F$99995>B2)))

 

[ravi2628]

Still not working for me?

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Code	1	1	1	1	1	Rank1	Rank2	Rank3	Rank4	Rank5
2	44216Ascot1	14.0	27.5	28.0	6.5	9.0	27	18	16	30	29
3	44216Ascot1	32.0	33.0	27.5	13.0	31.5	11	10	18	28	12
4	44216Ascot1	29.5	30.0	23.5	23.0		14	13	25	26
5	44216Ascot1	27.0	25.5	28.0			20	21	16
6	44216Ascot1	29.0	25.5	24.5			15	21	23
7	44216Ascot1	24.5					23
8	44216Ascot2	28.5	33.0	24.5	11.0	33.5	28	14	41	49	11
9	44216Ascot2	22.5	34.5	27.0	34.5	43.5	43	6	35	6	2
10	44216Ascot2	28.5	11.0	21.5	31.0	35.0	28	49	45	19	5
11	44216Ascot2	19.5	27.0	31.0	33.5	24.0	47	35	19	11	42
12	44216Ascot2	27.5	26.0	29.0	26.0	21.5	32	38	27	38	45
13	44216Ascot2	27.0	31.0	36.5	30.5	28.5	35	19	3	23	28
14	44216Ascot2	34.5	31.0	30.5	33.0	25.5	6	19	23	14	40
15	44216Ascot2	32.0	29.5	34.5	36.0	45.5	18	26	6	4	1
16	44216Ascot2	34.0	30.5	32.5	14.0	27.5	10	23	16	48	32
17	44216Ascot2	33.5	32.5	22.5	28.5	27.5	11	16	43	28	32
18	44216Ascot3	16.5	10.5	22.5	18.0	18.5	36	41	27	35	32
19	44216Ascot3	14.0	21.5	5.5	9.5	18.5	40	29	44	42	32
20	44216Ascot3	5.0	23.5	18.5	16.0	7.5	45	26	32	38	43
21	44216Ascot3	19.5	28.5	32.0			31	17	14
22	44216Ascot3	29.5	28.0	28.0	29.5	24.5	15	19	19	15	24
23	44216Ascot3	28.5	27.0	20.0			17	22	30
24	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
25	44216Ascot3	28.0	27.0	24.5			19	22	24
26	44216Ascot3	22.0	16.5				28	36
27	44216Ascot3	14.5					39
28	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
29	44216Ascot3	0.0	0.0	0.0	0.0	0.0	46	46	46	46	46
Sheet1

Cell Formulas
Range	Formula
G2:K29	G2	=IF(B2="","",1+SUMPRODUCT(($A$2:$A$99995=$A2)*($B$2:$F$99995>B2)))

Does it need to exclude zero "0" also in ranking

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Code	1	1	1	1	1	Rank1	Rank2	Rank3	Rank4	Rank5
2	44216Ascot1	14	28	28	6.5	9	18	9	7	21	20
3	44216Ascot1	32	33	28	13	32	2	1	9	19	3
4	44216Ascot1	30	30	24	23		5	4	16	17
5	44216Ascot1	27	26	28			11	12	7
6	44216Ascot1	29	26	25			6	12	14
7	44216Ascot1	25					14
8	44216Ascot2	29	33	25	11	34	28	14	41	49	11
9	44216Ascot2	23	35	27	35	44	43	6	35	6	2
10	44216Ascot2	29	11	22	31	35	28	49	45	19	5
11	44216Ascot2	20	27	31	34	24	47	35	19	11	42
12	44216Ascot2	28	26	29	26	22	32	38	27	38	45
13	44216Ascot2	27	31	37	31	29	35	19	3	23	28
14	44216Ascot2	35	31	31	33	26	6	19	23	14	40
15	44216Ascot2	32	30	35	36	46	18	26	6	4	1
16	44216Ascot2	34	31	33	14	28	10	23	16	48	32
17	44216Ascot2	34	33	23	29	28	11	16	43	28	32
18	44216Ascot3	17	11	23	18	19	23	28	14	22	19
19	44216Ascot3	14	22	5.5	9.5	19	27	16	31	29	19
20	44216Ascot3	5	24	19	16	7.5	32	13	19	25	30
21	44216Ascot3	20	29	32			18	4	1
22	44216Ascot3	30	28	28	30	25	2	6	6	2	11
23	44216Ascot3	29	27	20			4	9	17
24	44216Ascot3	0	0	0	0	0
25	44216Ascot3	28	27	25			6	9	11
26	44216Ascot3	22	17				15	23
27	44216Ascot3	15					26
28	44216Ascot3	0	0	0	0	0
29	44216Ascot3	0	0	0	0	0
Sheet4

Cell Formulas
Range	Formula
G2:K29	G2	=IF(OR(B2="",B2=0),"",1+SUMPRODUCT(($A$2:$A$99995=$A2)*($B$2:$F$99995>B2)))

 

[Obewan]

Does it need to exclude zero "0" also in ranking

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Code	1	1	1	1	1	Rank1	Rank2	Rank3	Rank4	Rank5
2	44216Ascot1	14	28	28	6.5	9	18	9	7	21	20
3	44216Ascot1	32	33	28	13	32	2	1	9	19	3
4	44216Ascot1	30	30	24	23		5	4	16	17
5	44216Ascot1	27	26	28			11	12	7
6	44216Ascot1	29	26	25			6	12	14
7	44216Ascot1	25					14
8	44216Ascot2	29	33	25	11	34	28	14	41	49	11
9	44216Ascot2	23	35	27	35	44	43	6	35	6	2
10	44216Ascot2	29	11	22	31	35	28	49	45	19	5
11	44216Ascot2	20	27	31	34	24	47	35	19	11	42
12	44216Ascot2	28	26	29	26	22	32	38	27	38	45
13	44216Ascot2	27	31	37	31	29	35	19	3	23	28
14	44216Ascot2	35	31	31	33	26	6	19	23	14	40
15	44216Ascot2	32	30	35	36	46	18	26	6	4	1
16	44216Ascot2	34	31	33	14	28	10	23	16	48	32
17	44216Ascot2	34	33	23	29	28	11	16	43	28	32
18	44216Ascot3	17	11	23	18	19	23	28	14	22	19
19	44216Ascot3	14	22	5.5	9.5	19	27	16	31	29	19
20	44216Ascot3	5	24	19	16	7.5	32	13	19	25	30
21	44216Ascot3	20	29	32			18	4	1
22	44216Ascot3	30	28	28	30	25	2	6	6	2	11
23	44216Ascot3	29	27	20			4	9	17
24	44216Ascot3	0	0	0	0	0
25	44216Ascot3	28	27	25			6	9	11
26	44216Ascot3	22	17				15	23
27	44216Ascot3	15					26
28	44216Ascot3	0	0	0	0	0
29	44216Ascot3	0	0	0	0	0
Sheet4

Cell Formulas
Range	Formula
G2:K29	G2	=IF(OR(B2="",B2=0),"",1+SUMPRODUCT(($A$2:$A$99995=$A2)*($B$2:$F$99995>B2)))

You are getting the right answers but I am not - it still appears to be ranking all the blank cells as 1 then the numbers tart getting ranked thereafter - this is weird as it's working for you?

 

[Obewan]

You are getting the right answers but I am not - it still appears to be ranking all the blank cells as 1 then the numbers tart getting ranked thereafter - this is weird as it's working for you?

Is there a way it could count all the blank cells and then we deduct them from the ranks?