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:

87	98	100	69	91	99	83	86	79	95	87	63	90	85	=# of cells that sum up to 1000 (in this case 11)
92	90	98	85	100	79	96	100	89	73					=# of cells that sum up to 1000 (in this case 10)​ <strike></strike>
etc.

<tbody>
</tbody>

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!!