update absolute reference everytime there is a 1 in adjacent column

[testinglife]

I am trying to find a way to have my formula update it's absolute refence to the current row whenever there is a 1 in the cell next to it. For example looking at the image I attached to this post. I want it to have an absolute reference starting at $B$1 because there is a 1 in A1. However at A5 there is a new 1 so I want the formula in C5 to update it's absolute reference to $C$5 now. That way I can just copy it down a massive list and have it do that accumulating sum feature up to the next 1 then stop and start it again for each different grouping. The intervals between the new 1's are not constant. There can be many or few rows between the next 1.

Thanks for the help in advance.

 

[Toadstool]

Hi Testinglife,

If you just want to restart the count then how about this:

Testinglife.xlsx
	A	B	C
1	1	10	10
2	2	14	24
3	3	200	214
4	4	32	232
5	1	40	40
6	2	28	68
7	3	33	61
8	4	27	60
9	5	30	57
10	6	50	80
11	7	333	383
12	1	23	23
13	2	13	36
14	3	32	45
15	1	333	333
16	2	44	377
17	3	43	87
Sheet1

Cell Formulas
Range	Formula
C1	C1	=B1
C2:C17	C2	=IF(A2=1,B2,B2+B1)

 

[testinglife]

Hi Testinglife,

If you just want to restart the count then how about this:

Testinglife.xlsx
	A	B	C
1	1	10	10
2	2	14	24
3	3	200	214
4	4	32	232
5	1	40	40
6	2	28	68
7	3	33	61
8	4	27	60
9	5	30	57
10	6	50	80
11	7	333	383
12	1	23	23
13	2	13	36
14	3	32	45
15	1	333	333
16	2	44	377
17	3	43	87
Sheet1

Cell Formulas
Range	Formula
C1	C1	=B1
C2:C17	C2	=IF(A2=1,B2,B2+B1)

Thanks, That help alot. I used a version of that to make it work. I was originally hoping to get everything into one massive formula but I settled for doing it accross many columns and using your idea to make it work.

 

[Toadstool]

Glad I could help.