Incremental summing to reset when a value is realised

[MAR17A]

Hi,

First time user!

I have tried a lot of formulae, however am still stuck on this. I need a formula that sum incrementally, however resets once 0 (column H) is identified.

1) I am currently using =IF(H2>0,SUM(H2:INDEX(H:H,MATCH(TRUE,(H:H<>0),0))),"") (column K), and this works to a certain point, however it doesn't reset when 0 is identified in column H. See image.

2) I also need a formula for identifying when column H (No of increments, exceed 24, sequentially)

Any assistance is appreciated.

Cheers,
MAR17A

 

[bebo021999]

Is it?

=IF(H2=0,"",SUM(INDEX(H2:OFFSET($H$1,LOOKUP(2,1/($H$1:H1=0),ROW($H$1:H1)),),)))

 

[MAR17A]

=IF(H2=0,"",SUM(INDEX(H2:OFFSET($H$1,LOOKUP(2,1/($H$1:H1=0),ROW($H$1:H1)),),)))

Unfortunately no, this just gave me the reverse of what I already have in column k. See column L.

 

[bebo021999]

Make sure you place the formula in L2 then drag down.

 

[Peter_SSs]

Try this

22 08 31.xlsm
	H			K
1	No of Increments			Incremental Running total
2	1			1
3	2			3
4	3			6
5	4			10
6	0
7	1			1
8	2			3
9	0
10	1			1
Reset sum

Cell Formulas
Range	Formula
K2:K10	K2	=IF(H2=0,"",H2+N(K1))

 

[MAR17A]

Try this

Try this

22 08 31.xlsm
	H			K
1	No of Increments			Incremental Running total
2	1			1
3	2			3
4	3			6
5	4			10
6	0
7	1			1
8	2			3
9	0
10	1			1
Reset sum

Cell Formulas
Range	Formula
K2:K10	K2	=IF(H2=0,"",H2+N(K1))

22 08 31.xlsm
	H			K
1	No of Increments			Incremental Running total
2	1			1
3	2			3
4	3			6
5	4			10
6	0
7	1			1
8	2			3
9	0
10	1			1
Reset sum

Cell Formulas
Range	Formula
K2:K10	K2	=IF(H2=0,"",H2+N(K1))

Make sure you place the formula in L2 then drag down.

Thank you beboo021999, I did this.

 

[MAR17A]

Thank you beboo021999, I did this.

Thank you Peter _SSs, this seemed to work or given me a result I can work with.

Are you able to solve my number 2 question. I need a formula to display a result when incrementally or sequentially, it reaches or exceeds a number. For example, using mine (or what you have returned) I want to place a number or text say "PP" when column K equals or is > 10 sequentially, otherwise leave ""? I have been staring at it too long and know that it is not that complex.

 

[Peter_SSs]

Thank you Peter _SSs, this seemed to work or given me a result I can work with.

You're welcome. Thanks for the confirmation.

Are you able to solve my number 2 question.

Could you show us some sample data and expected results as I'm not certain that I fully understand.
Even better if you could use XL2BB

 

[MAR17A]

You're welcome. Thanks for the confirmation.


Could you show us some sample data and expected results as I'm not certain that I fully understand.
Even better if you could use XL2BB

Thanks Peter_SSs, but I have solved it and that is thanks to you solving my first one. Cheers, M

 

[Peter_SSs]

I have solved it

Cheers. Thanks for letting us know.