Count Instances Since Last Non-Zero Value in Other Column

[khammer]

I would like to count the number of occurrences of each Item type in column A since the last non-zero value in the Failures column. Each case of a failure effectively resets the counter for that item type.

The desired example result is in bold below. Your input is appreciated.

[TABLE="width: 363"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Irrelevant [/TD]
[TD]Failures[/TD]
[TD]Instances Since Last Failure[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Try this, copied down.

Excel Workbook
	A	C	D
1	Item	Failures	Since Last Failure
2	C	0	1
3	B	0	1
4	A	1	0
5	C	2	0
6	C	0	1
7	B	0	2
8	A	0	1
9	C	0	2
Count since failure

 

[khammer]

Peter,

I wish to thank you for a most elegant solution!

I am very thankful for your level of contribution, as well as my new knowledge of Aggregate.

K

 

[Peter_SSs]

You're welcome, and thanks for your kind remarks.