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.

1	1		1
1	1
1	1	1		1
			1
1		1		1

<tbody>
</tbody>

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).