help with multi row formula tool to indicate activities between an event

[jbuszin]

I have data below on the emails that have been sent to 3 people below. I have the dates that the emails were sent and opened. The "opened" column is a simple 0/1 indicator if the email was opened. I also have created a field that tells me the number of days between emails being sent, which I used with the multi row formula tool. I'm struggling to create my next variable, which is the far right column that I want to create. I want there to be an indicator of how many emails were sent between an email that was opened and the previous email that was opened.  So if an email wasn't opened, that row would be null. Here is some mock data...what say you?

ID	SENT_DATE	Opened	Days_between_sent_emails	Column to be created
1	5/21/19	1	7	1
1	6/11/19	1	21	1
1	6/18/19	0	7
1	6/25/19	1	7	2
1	7/2/19	1	7	1
2	11/12/19	1	7	1
2	11/19/19	1	7	1
2	11/21/19	0	2
2	11/21/19	1	0	2
2	11/25/19	1	4	1
2	11/26/19	0	1
2	12/3/19	1	7	2
2	12/10/19	0	7
2	12/17/19	0	7
2	12/24/19	1	7	3
2	12/27/19	1	3	1
2	12/31/19	1	4	1
3	1/7/20	1	7	1
3	1/14/20	1	7	1
3	1/21/20	0	7
3	1/21/20	0	0
3	1/28/20	0	7
3	1/31/20	1	3	4
3	2/4/20	1	4	1

[AbhilashR · Accepted solution]

Hi @jbuszin, the attached solution should help you compute the column you are looking to create. Screenshot of the final output:

Let us know if this isn't what you are looking for.

[jbuszin]

thank you!