IF Formula to calculate time based on status in another column

[floggingmolly]

I have a spreadsheet with a Status Column which is either N for new or O for opened. Then a column for the Transaction Time. And a column for Reference ID. I have a formula to calculate the time difference if for example Row 1 is N, and Row 2 is O and the reference ID matches. The problem is when there are say 2 rows consecutively with the status O and the same reference ID. In that case I just want to get the value from the row above it for the open time. Example:

Column J Column K Column L Column M
Msg Status Transaction Time Time Taken Reference #
N 12/4/2020 13:06 00001
O 12/4/2020 13:13 0.005115741 00001
N 12/16/2020 11:21 00002
O 12/16/2020 11:26 0.046342593 00002
O 12/17/2020 11:15 00002

So in the example above, everything works in my formula, except I would like for the second status of O I would like to have the value for time taken since it has the same Reference #. So the second message status of O can be either the value from the row above 0.046342593 since this is actually the first time they viewed the message, or it can the difference between the N status date 12/16/2020 and the O status of 12/17/2020, either one will suffice for what I am doing. The formula I have so far is =IF(AND(J2="N",M2=[@[ZG_REFERENCE_ID]]),[@[TRANSACTION_TIME]]-K2,"").

Any help would be appreciated. I can't seem to get it right.

 

[ExceLoki]

here my results of the time differences do not match yours, but hopefully you can use this to get yours to work how you want.
---------------

Book1
	J	K	L	M	N	O
1	Msg Status	Transaction Time	Time Taken	Reference ID
2	N	12/4/20 13:06		1
3	O	12/4/20 13:13	0.005115741	1		0.004861111
4	N	12/16/20 11:21		2
5	O	12/16/20 11:26	0.046342593	2		0.003472222
6	O	12/17/20 11:15		2		0.003472222
Sheet1

Cell Formulas
Range	Formula
O2:O6	O2	=IF(J2="N","",INDEX(Table1[[ Transaction Time ]],MATCH(1,(M2=Table1[Reference ID])*("O"=Table1[Msg Status]),0))-INDEX(Table1[[ Transaction Time ]],MATCH(1,(M2=Table1[Reference ID])*("N"=Table1[Msg Status]),0)))