How to find the number of cells in a range (i.e. the size of the range) the sum of which is less than or equal to a certain number?

[Rnkhch]

Hello,

I have ranges of numbers in rows, and in each row, I want to find the number of non-empty cells in the range A:N, such that the sum of the range is less than or equal to 1000:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]87[/TD]
[TD]98[/TD]
[TD]100[/TD]
[TD]69[/TD]
[TD]91[/TD]
[TD]99[/TD]
[TD]83[/TD]
[TD]86[/TD]
[TD]79[/TD]
[TD]95[/TD]
[TD]87[/TD]
[TD]63[/TD]
[TD]90[/TD]
[TD]85[/TD]
[TD]=# of cells that sum up to 1000 (in this case 11)[/TD]
[/TR]
[TR]
[TD]92[/TD]
[TD]90[/TD]
[TD]98[/TD]
[TD]85[/TD]
[TD]100[/TD]
[TD]79[/TD]
[TD]96[/TD]
[TD]100[/TD]
[TD]89[/TD]
[TD]73[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

=# of cells that sum up to 1000 (in this case 10)​

<strike></strike>[/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks a lot for any input!

 

[Marcelo Branco]

Try this array formula in O1 copied down
=MATCH(1000,IF(A1:N1<>"",SUBTOTAL(9,OFFSET(A1,,,1,COLUMN(A1:N1)-COLUMN(A1)+1)),""))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Rnkhch]

Amazing! Thanks a lot!!