dynamic value for use in offset calculation

[ClairePS]

In my spreadsheet I have a column that contains either 0 or 1. In another column I want to use a formula to return the number of rows required such that the sum of those rows is 10.
So far I have got this
{=SMALL(IF(ISNUMBER(SEARCH("1",$M$1:$M125)),ROW($M$1:$M125),""),9)}
however I can't work out how to extend the formula down .
thankyou

 

[NARAYANK991]

Hi ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=MATCH(10, MMULT(N(TRANSPOSE(100 - ROW($A$16:$A$100))>=(100 - ROW($A$16:$A$100))),$A$16:$A$100), 0)

This will return the number of rows , starting from row 16 , required to get a sum of 10 from the set of values in the range A16:A100.

Narayan