"RANKIF" using SUMPRODUCT with dynamic range help

[millawitch]

Hi all,

I'm new to the MrExcel forum and this is my first post.

I have a "RANKIF" formula using SUMPRODUCT:

=SUMPRODUCT(--($B4=$B$4:$B$100),--($F4<$F$4:$F$100))+1

which works fine. At the moment "F" is the last column, but the table will expand to the right so the last column reference will always change. In other words, $F4<$F$4:$F$100 can change to $G4<$G$4:$G$100, $H4<$H$4:$H$100, etc and I want the formula to always look for data in the last column.

How do I make it work in this scenario?

Thanks!

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum,

Assuming there is a header in the last column, in row 3, and it is Scores (adjust to suit) maybe this can work

=SUMPRODUCT(--($B4=$B$4:$B$100),--(INDEX($A$4:$Z$100,ROWS($1:1),MATCH("Scores",$3:$3,0)) < INDEX($A$4:$Z$100,0,MATCH("Scores",$3:$3,0))))+1

<INDEX($A$4:$z$100,0,match("scores",$3:$3,0))))+1
copy down

M.</INDEX($A$4:$z$100,0,match("scores",$3:$3,0))))+1

 

[millawitch]

Hi Marcelo,


Thanks for your input. At the moment I'm just starting to build this database but it will be converted into a Excel 2007 table, and you can't have column headers with repeated names, so this wouldn't work.

I tried to test your formula, but I didn't understand why the INDEX used $A$4:$Z$100,ROWS($1:1) if my table headers are in row 3 and data starts in row 4 (the RANKIF formula is in column A). Also, the last INDEX doesn't seem to be referencing a range, but I might be wrong.

I can't have VBA, but if adding helper cells makes it easier, then perhaps is the way to go.

I've tested with 2 helper cells on the side

H
9 6 =MAX(COUNTA($A4:$KN4))
10 $F$4 =ADDRESS(ROW($B4),$H$9)

and got =SUMPRODUCT(--($B4=$B$4:$B$100),--(INDIRECT($H10)<$F$4:$F$100))+1 to work, but don't know how to tackle the range part...


Any thoughts?

 

[Marcelo Branco]

Hi Marcelo,


Thanks for your input. At the moment I'm just starting to build this database but it will be converted into a Excel 2007 table, and you can't have column headers with repeated names, so this wouldn't work.

I tried to test your formula, but I didn't understand why the INDEX used $A$4:$Z$100,ROWS($1:1) if my table headers are in row 3 and data starts in row 4 (the RANKIF formula is in column A). Also, the last INDEX doesn't seem to be referencing a range, but I might be wrong.

I can't have VBA, but if adding helper cells makes it easier, then perhaps is the way to go.

I've tested with 2 helper cells on the side

H
9 6 =MAX(COUNTA($A4:$KN4))
10 $F$4 =ADDRESS(ROW($B4),$H$9)

and got =SUMPRODUCT(--($B4=$B$4:$B$100),--(INDIRECT($H10)<$F$4:$F$100))+1 to work, but don't know how to tackle the range part...


Any thoughts?

The last INDEX
INDEX($A$4:$Z$100,0,MATCH("Scores",$3:$3,0))
uses 0 as the second parameter (row number) to return an array - all the values present in the column. For example, if Scores (the supposed header) is in column F then it returns F4:F100.

Could you provide a data sample?

M.

 

[Marcelo Branco]

Hi Marcelo,


Thanks for your input. At the moment I'm just starting to build this database but it will be converted into a Excel 2007 table, and you can't have column headers with repeated names, so this wouldn't work.

The formula requires that there is a header in the column with the scores to be ranked (Scores or whatever, but i must know what it is), not repeated in anyway.

M.

 

[millawitch]

The formula requires that there is a header in the column with the scores to be ranked (Scores or whatever, but i must know what it is), not repeated in anyway.

M.

Hi Marcelo,

Thank you for your time and for explaining the INDEX "trick"! I don't know Excel that much, I just bang my head hard enough in a wall until something works (with a lot of forum tips I might say). But as I said, I can't have the same header repeated as this will be a table.

 

[Marcelo Branco]

But as I said, I can't have the same header repeated as this will be a table.

Maybe i'm not making myself clear (English is my second language). As i said in #5 (above) the formula requires an unique header in row 3 in the column that contains the scores (values) to be ranked. With repeated headers the formula wouldn't work

Please, try to post a data sample. To do this:
Select a relevant part of your data, say 10 rows, including headers
Put borders
Copy (Ctrl+C)
Paste (Ctrl+V) in the forum reply page.

Doing so someone can help you.

M.

 

[millawitch]

Actually, I got it to work creating 4 helper cells (with =ADDRESS(ROW($A6),$K$1) inside the table, copied down):

K1 Columns count: 11 =MAX(COUNTA($A$5:$NY$5))</SPAN>
K2 Last column: $K$100 =ADDRESS(100,$K$1)</SPAN>
K3 Range: $K$6:$K$100 =ADDRESS(ROW($A6),$K$1)&":"&ADDRESS(100,$K$1)</SPAN>

and the RANKIF function as

=SUMPRODUCT(--($C6=$C$6:$C$100),--(INDIRECT($B6)<INDIRECT($K$3)))+1

A bit cumbersome - does someone have a better solution? I'd appreciate some help!

 

[Marcelo Branco]

</SPAN>

and the RANKIF function as

=SUMPRODUCT(--($C6=$C$6:$C$100),--(INDIRECT($B6)<INDIRECT($K$3)))+1

A tip:
When posting a formula that uses less than put spaces around the symbol < . Otherwise the forum software takes it as a special character and truncates the formula.

One more time: we need a data sample to see if a better solution is possible.

M.

 

[millawitch]

A tip:
When posting a formula that uses less than put spaces around the symbol < . Otherwise the forum software takes it as a special character and truncates the formula.

One more time: we need a data sample to see if a better solution is possible.

M.

Oops, only now I saw that the formula was cut out. Here's a sample (columns E-G and I will have data but are irrelevant for this):

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1										Columns count:	11
2										Last column:	$K$100
3										Range:	$K$6:$K$100
4
5	Rank	Last Column	Subgroup	Shareholder	E	F	G	Shares Received	I	Total Shares	Percentage
6	1	$K$6	Totals	Totals				1,392,710		1,392,710	100.00%
7	1	$K$7	Investor Group A	Investor Group A						1,357,955	97.50%
8	2	$K$8	Investor Group A	Investor 1				1,250,000		1,250,000	89.75%
9	3	$K$9	Investor Group A	Investor 2				55,500		55,500	3.99%
10	4	$K$10	Investor Group A	Investor 3				52,455		52,455	3.77%
11	1	$K$11	Investor Group B	Investor Group B						34,755	2.50%
12	4	$K$12	Investor Group B	Investor 4				7,755		7,755	0.56%
13	3	$K$13	Investor Group B	Investor 5				12,000		12,000	0.86%
14	2	$K$14	Investor Group B	Investor 6				15,000		15,000	1.08%
Sample

Cell Formulas
Range	Formula
K1	=MAX(COUNTA($A$5:$NY$5))
K2	=ADDRESS(100,$K$1)
K3	=ADDRESS(ROW($A6),$K$1)&":"&ADDRESS(100,$K$1)
A6	=SUMPRODUCT(--($C6=$C$6:$C$100),--(INDIRECT($B6)$K$3)))+1
B6	=ADDRESS(ROW($A6),$K$1)