Need to sum up to 50 values until the sum is equal to or greater than a specific value

[TakiTaki]

Hi,

I have to add actual varying amounts in a range of numbers located in one row/column until a specified target amount is reached, then populate a cell with the earliest date the target was met.
I also need to be able to change the targets and have a new date generated.

Ty

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1	Date	Amount	------	Target	Date
2	8/1/2015	87		300	8/6/2015
3	8/2/2015	51
4	8/3/2015	59
5	8/4/2015	13
6	8/5/2015	82
7	8/6/2015	36
8	8/7/2015	93
9	8/8/2015	90
10	8/9/2015	15

This array formula** entered in E2:

=INDEX(A2:A10,MATCH(TRUE,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B10)-ROW(B2)+1))>=D2,0))

Format as Date

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If the target value is not met the formula will return an #N/A error.

[TakiTaki]

Hi, Thank you for your reply-

After changing the info to reflect my data, did the ctrl/shift/ent and I'm getting a date value that is not listed in the table and when I change the target number, the formula doesn't give a different answer. (target # is a link)

[oeldere]

How about sharing the file, without confidential information?

[TakiTaki]

Its working in the share file now but not my own yet....
I've attached so people can see what I meant and offer additional methods they would use as well
However the formula is working perfectly now in the share file and updates to the correct value when I change the targets-
The only difference between the two is the custom mask of the date (one style vs another)


Earliest Date.jpg

[Tony Valko]

Here's another option...

Data Range

	A	B	C	D	E	F
1	Date	Amount	Subtotal	------	Target	Date
2	8/1/2015	87	87		300	8/6/2015
3	8/2/2015	51	138
4	8/3/2015	59	197
5	8/4/2015	13	210
6	8/5/2015	82	292
7	8/6/2015	36	328
8	8/7/2015	93	421
9	8/8/2015	90	511
10	8/9/2015	15	526

Enter this formula in C2 and copy down as needed:

=SUM(B$2:B2)

Enter this array formula** in F2:

=INDEX(A2:A10,MATCH(TRUE,C2:C10>=E2,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[TakiTaki]

Its working in my file now as well. How do I update thread to "solved"?

[Tony Valko]

Good deal. Thanks for the feedback!

To mark a thread as being solved...

Immediately above post #1 there is a menu bar.

From that menu bar select Thread Tools then select Mark this thread as solved...