Counting number of cells that make up a sum..
 

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?

Counting number of cells that make up a sum..
 

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!

In article ,
tim m wrote:

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?

Counting number of cells that make up a sum..
 

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

"Domenic" wrote:

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!

In article ,
tim m wrote:

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?