Sum Cells and IF Statement to add times

[EBoudreau]

I have a table where I record the start time and end time for Frac and Wireline operations on a well, and that process is repeated for each stage of the well. Sometimes we skip stages and no times are entered indicating that skip which messed up the Total Time calculation.

I left "Stage 3" (Row 112) blank to show what happens if we skipped that stage and moved on to "Stage 4" (Row 113). How can the formulas in cell I110 and on down the sheet be modified so it would omit the blank cell in the continue to add the times as we progress down the sheet not giving an error when a gap like shown occurs?

For appearance reasons, I would also like to cells below the last populated one in column I to remain blank until a value is calculated in Column H. For example, only calculate cell I115 when H115 is calculated with a value and so on down the sheet.

What that explained clearly?

COT Frac Report (REVISING).xlsm
	A	B	C	D	E	F	G	H	I
107	Stage Times			West QA			Last Stage #:		37
108	Stage #	Frac Open	Frac Close	Frac Stage Time	WL Open	WL Close	WL Stage Time	Stage Time	Total Time
109	TOE	1:00	2:45	1:45			0	1:45	1:45
110	1	2:50	4:15	1:25	1:23	2:45	1:22	2:47	4:32
111	2	5:50	7:30	1:40	4:20	5:34	1:14	2:54	7:26
112	3			0			0
113	4	9:30	11:45	2:15	7:50	9:15	1:25	3:40	#VALUE!
114	5			0	11:55	13:45	1:50	1:50	#VALUE!
115	6			0			0
116	7			0			0
117	8			0			0
118	9			0			0
119	10			0			0
120	11			0			0
121	12			0			0
122	13			0			0
123	14			0			0
124	15			0			0
125	16			0			0
126	17			0			0
127	18			0			0
128	19			0			0
129	20			0			0
130	21			0			0
131	22			0			0
132	23			0			0
133	24			0			0
134	25			0			0
135	26			0			0
136	27			0			0
137	28			0			0
138	29			0			0
139	30			0			0
140	31			0			0
141	32			0			0
142	33			0			0
143	34			0			0
144	35			0			0
145	36			0			0
146	37			0			0
147	38			0			0
148	39			0			0
149	40			0			0
150	41			0			0
151	42			0			0
152	43			0			0
153	44			0			0
154	45			0			0
155	46			0			0
156	47			0			0
157	48			0			0
158	49			0			0
159	50			0			0
160	51			0			0
161	52			0			0
162	53			0			0
163	54			0			0
164	55			0			0
165	56			0			0
166	57			0			0
167	58			0			0
168	59			0			0
169	60			0			0
170	61			0			0
171	62			0			0
172	63			0			0
173	64			0			0
174	65			0			0
175	66			0			0
176	67			0			0
177	68			0			0
178	69			0			0
179	70			0			0
180	71			0			0
181	72			0			0
182	73			0			0
183	74			0			0
184	75			0			0
185	76			0			0
186	77			0			0
187	78			0			0
188	79			0			0
189	80			0			0
190	81			0			0
191	82			0			0
192	83			0			0
193	84			0			0
194	85			0			0
195	86			0			0
196	87			0			0
197	88			0			0
198	89			0			0
199	90			0			0
200	91			0			0
201	92			0			0
202	93			0			0
203	94			0			0
204	95			0			0
205	96			0			0
206	97			0			0
207	98			0			0
208	99			0			0
209	100			0			0
210		Frac Total Hrs:		7:05	WL Total Hrs:		5:51	Total	12:56
Stage Times

Cell Formulas
Range	Formula
D107	D107	=IF('Frac Report'!C17="","",'Frac Report'!C17)
I107	I107	=IFERROR(IF(COUNTIF('Frac Report'!F17:DB17,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F17:DB17,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F17:DB17,0))),"")
H109:H209	H109	=IF(SUM(D109,G109)=0,"",D109+G109)
D109:D209,G109:G209	D109	=IF(C109="","0",(MOD(C109-B109,1)))
D210,G210	D210	=SUM(D109:D209)
I109	I109	=H109
I110:I209	I110	=IF(H110="","",(H110+I109))
I210	I210	=SUM(D210,G210)

 

[Fluff]

How about

=IF(H110="","",sum(H110,I109))

 

[EBoudreau]

How about

=IF(H110="","",sum(H110,I109))

I tried that in I113 for example where

=IF(H113="","",sum(H113,I112))

All it did was give the result of H113 because there was nothing in I112. I would like it to add H113 to, in this case I111, or what ever is the next cell above that has data in it. Otherwise my total time does not add up like I mentioned in the original post.

How would I do it so that:
If both cells C112 and F112 are blank, the enter I111, else SUM(H112,I111).

 

[Fluff]

Good point, how about

=IF(H110="","",SUM(H$109:H110))

 

[EBoudreau]

Good point, how about

=IF(H110="","",SUM(H$109:H110))

Closer. how would I write a formula for this:

Formula for Cell H110:
First, If C110 and F110 are BLANK, do nothing, then second, if SUM of D110 and G110 = 0, Return "0:00".

 

[Fluff]

In what way doesn't that formula work?

 

[EBoudreau]

In what way doesn't that formula work?

Well it does work actually. I just didn't carry the formula down the column and test a few scenarios. It looks like it should be good to go now.

Thank you for your assistance!

 

[Fluff]

You're welcome & thanks for the feedback.