Find row containing maximum number of values

[bkalma]

I am trying to write an array formula that will return the row number for the row containing the maximum number of values in a set of data. For example, I would like the formula to return 3 for the following as row 3 contains the maximum number of data entries.

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]


I would also like this formula to work for any range.

The following (CTRL+SHIFT+ENTER) gives the maximum number of data points but not the row number:

=MAX(SUBTOTAL(2,OFFSET($A$1:$A$5,ROW($A$1:$E$5)-ROW($A$1:$A$5,0)))

Might be a good place to start.

Thanks!​

 

[Aladin Akyurek]

See your original thread: http://www.mrexcel.com/forum/excel-questions/975341-individually-count-multiple-rows.html (even if you are interested in numbers, not just 1).