Can you help calculate consecutive event occurs?

[DayVe3000]

Hi Guys,
I have a sheet where I am recording the number of days where an event happens (this is marked with a “1”). What I am wanting to do is I am wanting to count how many days from the last day where this event occurs (going back as far as the last 90 days).
I’ve read a through things via a Google search – but I’m still lost.

A brief example to my sheet can be seen below along with the results I'd expect in Collumn H if I could get a formulae to work.

Thanks in Advance for any help.

 

[MrKowz]

Welcome to the forums!

Try:

Excel 2013/2016
	A	B	C	D	E	F	G
1		7/27/2017	7/28/2017	7/30/2017	7/31/2017	8/1/2017	Result
2	Subject A	0	0	1	0	0	0
3	Subject B	1	1	1	1	1	5
4	Subject C	0	0	0	1	1	2
5	Subject D	1	1	0	0	1	1
6	Subject E	1	1	1	1	1	5
7	Subject F	1	1	0	1	1	2
Sheet2

Cell Formulas
Range	Formula
G2	{=COUNT(B2:F2)-IFERROR(LARGE(IF(B2:F2=0,COLUMN(B2:F2)-1),1),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.