Need Formula that Calculates Sums of Multiple Cycles Within Column

**AJRIGNEY** · 06-06-2012, 09:00 AM

http://www.mrexcel.com/forum/showthread.php?t=640203

example.xlsHey everyone. I'm currently a student and my studies have nothing to do with excel, but I have am in the middle of an internship where all I do is work with spreadsheets. Currently, I'm working with a paper company in Alabama, where they asked me to create a spreadsheet that measures efficiency. While I've made great progress on the project, I just came up on a little snag and was wondering if you might be able to help.

I was asked to calculate what is known as "cooktime" per every cycle (each cycle culminates in what is known as "blowtime". "Cooktime" refers to the total number of minutes measured from when pressure reaches a certain point until the blowtime.

A simple version of what I'm trying to do looks like this (Same example in .xls attachment):

A B C D
TIME HFACTOR BLOWPT COOKTIME
0:00 0.63457 NOBLOW
0:01 1.23741 NOBLOW
0:02 2.37568 NOBLOW
0:03 4.79514 NOBLOW
0:04 6.12894 NOBLOW
0:05 12.29853 NOBLOW
0:06 39.79284 BLOW
0:07 39.79284 BLOW
0:08 39.79284 BLOW
0:09 0.76581 NOBLOW
0:10 1.45827 NOBLOW
0:11 3.78290 NOBLOW
0:12 11.73659 NOBLOW
0:13 32.48762 NOBLOW
0:14 49.31267 BLOW
0:15 49.31267 BLOW

Essentially, I need a formula that will count "A" when "B" is >5, until C="BLOW" per each cycle, as there will be around 60

If there's any possible way you could help me, or if you think there is an easier way to do this, I'd be incredibly grateful.

Thanks for your time.

Andrew

**oeldere** · 06-06-2012, 09:10 AM

Probably you get better help, if you post your data in an excel-workbook (without confidentinal information).

**jeffreybrown** · 06-06-2012, 09:55 AM

Andrew, for the data in your attached spreadsheet, what is the answer to your query?

Maybe:
=SUMPRODUCT(--($B$2:$B$17>5),--($C$2:$C$17="BLOW"),--ISNUMBER($A$2:$A$17))

**AJRIGNEY** · 06-06-2012, 10:03 AM

Both d9 and d17 should equal 2. Basically, any data that is greater than 5 in column b and continues until it reaches "blow" is considered a cycle. I need the sum of each cycle, and because there were two cycles in the example, both d9 and d17 should equal 2.

**jeffreybrown** · 06-06-2012, 10:26 AM

Hi Andrew,

Why is D9 the end of the cycle when the first "BLOW" is in D8 and the last "BLOW" before the begginining of the next cycle is D10?

Does this come close to what you are looking for?

D2 and drag down >> =IF(AND(C2<>C3,B2>5,C2="Blow"),1,"")

**AJRIGNEY** · 06-06-2012, 10:40 AM

Jeff, seems like you're on the right track.

Good catch on the cycles. I was reading cells from another spreadsheet with similar info. Instead of the cells i said previously, D7 and D15 should say two.

The formula you gave me doesn't quite work the way I need. While it does indicate the end of each cycle, it doesn't total the sum of minutes passed in each cycle.

CYCLE STARTS: "NOBLOW" HAS HFACTOR > 5
CYCLE ENDS: ON THE "NOBLOW" IMMEDIATELY BEFORE BLOW

**AJRIGNEY** · 06-08-2012, 12:17 PM

Thanks to Mr. Girvin, who runs the EXCELISFUN channel on Youtube, I've got a working formula.

For future reference, here it is (though it was custom-tailored for my spreadsheet, it might be able to give some a headstart)
=IF(AND(F10=$I$6,F11=$J$6),COUNTIF(C10:INDEX(C10:$C$10,IF(ISNA(MATCH(1,1/(F10:$F$10=$J$6))+1),1,MATCH(1,1/(F10:$F$10=$J$6))+1)),$I$5),"")

This formula also did the trick:

=IF(A2+TIME(,1,)=A3,"",ROWS(INDEX($H$1:H1,LOOKUP(2,1/($H$1:H1<>""),ROW($H$1:H1))):H1))

**jeffreybrown** · 06-08-2012, 12:23 PM

Well that is great. Glad you found a working solution and I can promise you, I could not have come up with that. Mike does do a great job of explaining/breaking down a formula.

Is it possible you could post your sample workbook with the formula working for anybody searching for this same type of solution?