how many cells sum to X?

[Tim Zych]

If I have a list of numbers:

1
2
3
4
5
5

I want to return the number of cells that sum to X.

If X is 6, the value returned should be 3 (sum of 1,2,3),
starting at the beginning of the list and moving downward.

If X is 9, the value returned should be 4.

Thanks.

 

[Domenic]

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,,,{1,2,3,4,5,6}))>=B1,0)

OR

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,,,ROW(INDIRECT("1:6"))))>=B1,0)

....entered using CONTROL+SHIFT+ENTER.

If you have many rows of data, for example 100 rows, use the second
formula and change...

ROW(INDIRECT("1:6"))

to

ROW(INDIRECT("1:100"))

Hope this helps!

 

[Tim Zych]

Very nice! I hadn't considered Subtotal.
Thanks!

-----Original Message-----
=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,,,{1,2,3,4,5,6}))>=B1,0)

OR

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,,,ROW(INDIRECT("1:6"))))
=B1,0)

....entered using CONTROL+SHIFT+ENTER.

If you have many rows of data, for example 100 rows, use the second
formula and change...

ROW(INDIRECT("1:6"))

to

ROW(INDIRECT("1:100"))

Hope this helps!


.

 

[BenjieLop]

Hi Domenic,

I need to ask you this regarding the formula that you derived. Wher
did you get the "9" in your formula?

Thanks and regards.

 

[RagDyer]

Self-explanatory *IF* you lookup Subtotal() in the Help files!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


Hi Domenic,

I need to ask you this regarding the formula that you derived. Where
did you get the "9" in your formula?

Thanks and regards.

 

[BenjieLop]

RD,

Thank you for the tip ... shows you how lazy I can be sometimes.

 

[RagDyer]

Aren't we all ... sometimes !

RD,

Thank you for the tip ... shows you how lazy I can be sometimes.