Counting number of cells that make up a sum..

[tim m]

Greetings: Here is my problem,

I have a column of numbers (Building areas in sqm). The column will be
sorted from highest to lowest. I have a number from a calculation I obtain
elsewhere. I need to count the minimum and maximum number of cells in the
column that will be add up to be greater than my calculated number.

For example:

Column A
20000
10000
9000
8000
7000
5000
1000
500
300
200
100

My calculated number is 25000 thus I need to sum from the top of the
column. The 1st two cells sum > than 25000 thus my minimum is 2
I then need to sum from the bottom of the list until the sum > 25000. At
the 9000 cell the sum is > 25000 thus the maximum would be 9

(Of course I have alot more areas of varying sizes in the real spreadsheet.)

What formula might I use to accomplish this?

 

[Domenic]

Try the following formulas which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A2:A12,,,ROW(A2:A12)-ROW(A2)+1))>B2,0)

and

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A2:A12,ROWS(A2:A12)-1,,-(ROW(A2:A12)-ROW(A2
)+1)))>B2,0)

Hope this helps!

 

[tim m]

works nicely, thanks! Now I have to reverse engineer it to see why it works
for my own curiosity. :O)