"RANKIF" using SUMPRODUCT with dynamic range help

millawitch

millawitch

New Member
Joined
Jun 20, 2012
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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!
 
Sort by date Sort by votes

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
millawitch said:
Where did all the table borders go? Oh well... I hope it helps. :)
Click to expand...

Wanted to delete/edit the post above and to clear up the table a bit more, but admin message said I cannot edit the posts anymore. Column H is also irrelevant at this stage. Another go:


Excel 2007
ABCDEFGHIJK
1Columns count:11
2Last column:$K$100
3Range:$K$6:$K$100
4
5RankLast ColumnSubgroupShareholderEFGHITotal SharesPercentage
61$K$6TotalsTotals##1,392,710100.00%
71$K$7Investor Group AInvestor Group A1,357,95597.50%
82$K$8Investor Group AInvestor 1##1,250,00089.75%
93$K$9Investor Group AInvestor 2##55,5003.99%
104$K$10Investor Group AInvestor 3##52,4553.77%
111$K$11Investor Group BInvestor Group B34,7552.50%
124$K$12Investor Group BInvestor 4##7,7550.56%
133$K$13Investor Group BInvestor 5##12,0000.86%
142$K$14Investor Group BInvestor 6##15,0001.08%
Sample
Cell Formulas
RangeFormula
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)


Thanks for all the trouble!
 
Upvote 0
Hi,

My suggestion: only one helper cell, $J$1 ; and no need of column B as helper

[TABLE="width: 548"]
<tbody>[TR]
[TD="class: xl65, width: 21, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 64, bgcolor: #DCE6F1"]A
[/TD]
[TD="class: xl67, width: 112, bgcolor: #DCE6F1"]B
[/TD]
[TD="class: xl67, width: 112, bgcolor: #DCE6F1"]C
[/TD]
[TD="class: xl67, width: 26, bgcolor: #DCE6F1"]D
[/TD]
[TD="class: xl67, width: 26, bgcolor: #DCE6F1"]E
[/TD]
[TD="class: xl67, width: 26, bgcolor: #DCE6F1"]F
[/TD]
[TD="class: xl67, width: 26, bgcolor: #DCE6F1"]G
[/TD]
[TD="class: xl67, width: 26, bgcolor: #DCE6F1"]H
[/TD]
[TD="class: xl67, width: 82, bgcolor: #DCE6F1"]I
[/TD]
[TD="class: xl67, width: 77, bgcolor: #DCE6F1"]J
[/TD]
[TD="class: xl67, width: 64, bgcolor: #DCE6F1"]K
[/TD]
[TD="class: xl67, width: 64, bgcolor: #DCE6F1"]L
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]1
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]LastColumn
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]2
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]3
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]4
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]5
[/TD]
[TD="class: xl69, bgcolor: transparent"]Rank
[/TD]
[TD="class: xl65, bgcolor: transparent"]Subgroup
[/TD]
[TD="class: xl65, bgcolor: transparent"]Shareholder
[/TD]
[TD="class: xl65, bgcolor: transparent"]E
[/TD]
[TD="class: xl65, bgcolor: transparent"]F
[/TD]
[TD="class: xl65, bgcolor: transparent"]G
[/TD]
[TD="class: xl65, bgcolor: transparent"]H
[/TD]
[TD="class: xl65, bgcolor: transparent"]I
[/TD]
[TD="class: xl65, bgcolor: transparent"]Total Shares
[/TD]
[TD="class: xl65, bgcolor: transparent"]Percentage
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]6
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]Totals
[/TD]
[TD="class: xl65, bgcolor: transparent"]Totals
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1.392.710
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]100,00%
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]7
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor Group A
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor Group A
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1.357.955
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]97,50%
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]8
[/TD]
[TD="class: xl69, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor Group A
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor 1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1.250.000
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]89,75%
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]9
[/TD]
[TD="class: xl69, bgcolor: transparent"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor Group A
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor 2
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]55.500
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3,99%
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]10
[/TD]
[TD="class: xl69, bgcolor: transparent"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor Group A
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor 3
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]52.455
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3,77%
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]11
[/TD]
[TD="class: xl69, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor Group B
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor Group B
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]34.755
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2,50%
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]12
[/TD]
[TD="class: xl69, bgcolor: transparent"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor Group B
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor 4
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]7.755
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0,56%
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]13
[/TD]
[TD="class: xl69, bgcolor: transparent"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor Group B
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor 5
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]12.000
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0,86%
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]14
[/TD]
[TD="class: xl69, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor Group B
[/TD]
[TD="class: xl65, bgcolor: transparent"]Investor 6
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]15.000
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1,08%
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #DCE6F1"]15
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Formula in J1
=MATCH("Percentage",$5:$5,0)

Formula in A6
=SUMPRODUCT(--($B$6:$B$1000=$B6),--(INDEX($A$6:$NY$1000,0,$J$1)>INDEX($A$6:$NY$1000,ROWS($1:1),$J$1)))+1
copy down

M.
 
Last edited:
Upvote 0
Special thanks for Aladin,

He reminded me, through a friendly PM, to insert helper cell (J1) in the formula, instead of hard coding 10

Thank you, my friend.

I owe you one

M.
 
Upvote 0
Marcelo Branco said:
Formula in J1
=MATCH("Percentage",$5:$5,0)
Click to expand...

Hi Marcelo,

I think I CANNOT use the "MATCH a word" funtion as this data will be converted into an Excel 2007 table and it doesn't allow columns with repeated labels / headers. :( As in:

Insert a table


  • On a worksheet, select the range of cells that you want to include in the table. The cells can be empty or can contain data.
  • On the Insert tab, in the Tables group, click Table.</STRONG></STRONG></STRONG>

ZA010165438.gif



</STRONG></STRONG></STRONG>Unless this formula allows for partial match I guess... At the moment I have 5 round of investments each with 5 columns that contain similar headers (see sample headers below):

Excel 2007
R
S
T
U
V
W
1
AMOUNT INVESTED at £3.80
SHARES RECEIVED FOR INVESTMENT at £3.80
SHARE TRANSFERS DURING THIS £3.80 ROUND
TOTAL SHARES HELD AFTER THIS INVESTMENT at £3.80

<TBODY>
[TD="bgcolor: #bfbfbf"]PERCENTAGE OF TOTAL SHARES AFTER THIS INVESTMENT at £3.33
[/TD]

[TD="bgcolor: #bfbfbf"]PERCENTAGE OF TOTAL SHARES AFTER THIS INVESTMENT at £3.80
[/TD]

</TBODY>
Sample2​

Can you confirm? Thank you again for your time! :)
 
Upvote 0
Sorry, i'm not understanding your problem...

Couldn't you use the proper text (header) in the MATCH function depending on which column you do want to rank? Ie changing the last part either 3.33 or 3.80.

By the way, yes is possible to do partial matches, but i can't see where this would apply.

M.
 
Upvote 0
Another possibility.

If you always want to know the position of the last column in row 5 with text (the last header) you can use in J1
=MATCH(REPT("z",255),$5:$5)

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Pairwise Comparison Table
Replies
4
Views
356
Samgraphics
S
N
  • Solved
Sumproduct with Table Rows doesn't work
Replies
8
Views
228
Nicha
N
B
  • Question Question
Rank formula to include cells with error.
Replies
2
Views
565
bcom93
B
M
  • Solved
Absolute Reference - Change absolute range without manual intervention
Replies
7
Views
458
Peter_SSs
Peter_SSs
drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
247
drom
drom

Forum statistics

Threads
1,223,447
Messages
6,172,206
Members
452,448
Latest member
Tupacandres

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top