Conditional Running Total

[ghrain22]

Hello,

I am trying to figure out a formula that will give the the running total sum of a list of data for descending dates, but stop once the running total (of 'X') hits a certain number (say 100).

Input(Dates in M/D/YY):

ID	Date	X	Y
a	4/1/16	50	2
a	3/1/16	32	4
a	2/1/16	37	6
a	1/1/16	25	8
b	4/1/16	17	2
b	3/1/16	88	4
b	2/1/16	52	6
b	1/1/16	17	8

<tbody>
</tbody>

In this example, a would return the sum of 50+32+37=119 (the first instance of the running total being greater than 100) and the corresponding running total of y which was 2+4+6=12

output:

ID	X	Y
a	119	12
b	105	6

<tbody>
</tbody>

I've tried complex array formulas and a variety of lookups, but can't seem to crack this one. Any help would be greatly appreciated.

Thanks!
~Ghrain22

 

[Eric W]

Single cell formulas that pick a value out of various subtotals are notoriously difficult. There are a few techniques, but I couldn't come up with anything that works for your situation. One thing you could do is use a helper column:

	A	B	C	D	E
1	ID	Date	X	Y
2	a	4/1/2016	50	2	50
3	a	3/1/2016	32	4	82
4	a	2/1/2016	37	6	119
5	a	1/1/2016	25	8	144
6	b	4/1/2016	17	2	17
7	b	3/1/2016	88	4	105
8	b	2/1/2016	52	6	157
9	b	1/1/2016	17	8	174
10
11	ID	X	Y	Cutoff
12	a	119	12	100
13	b	105	6	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula E2 =IF(A2<>A1,C2,E1+C2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B12 {=MIN(IF((A$2:A$9=A12)*(E$2:E$9>D12),E$2:E$9))} C12 {=SUM(IF(($A$2:$A$9=A12)*($E$2:$E$9<=B12),$D$2:$D$9))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the formula in E2 and copy down the column. Then put the B12 and C12 formulas in with Control+Shift+Enter, then copy down the columns. This should work, but it assumes that all the names are grouped.

Hope this helps.