Count with condition range

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

Hello,

I need some help! I'm trying to count the number of times a specific
pressure is not just reached, but maintained for a certain time period. I
have a trend list where a measurement program has dumped data into Excel for
certain points in time. For example:

Column A Column B Pressure
1/1/08 11:29AM 53.50
1/1/08 3:29PM 83.76
1/1/08 7:29PM 89.54
1/2/08 11:29PM 50.12

So, what I need to be able to do is count the number of times that the
pressure not just goes above 80, but stays there for at least 8 hours, let's
say (so it would not count the above example, because it only stayed above
80...which I realize is not a perfect example because they're 4 hour
increments and it could have theoretically stayed above 80 longer...anyway).
Any ideas? Any help is appreciated--thanks!
 
This seems to work...

First thing, your times are not true Excel times. A true Excel recognized
time has a space before AM/PM:

11:29AM = not a recognized time
11:29 AM = recognized time

So, assuming you get the times straightened out...

Another assumption: the data is in ascending order like the sample you
posted...

The row above your data needs to be empty...

So, assuming your data starts in cell A2...

Enter this formula in D2:

=IF(AND(C1>=80,C2>=80),((A2+B2)-(A1+B1))*24,"")

Enter this formula in E2:

=IF(D2="","",IF(D3="",SUM(D$2:D2)-SUM(E1:E$2),""))

Select both D2 and E2 and copy down to the end of data.

To get the count that meets your conditions:

=COUNTIF(E:E,">=8")
 
You're welcome. Thanks for the feedback!

I have to admit, this was quite a challenge. I was racking my brain trying
to do this with a single formula and was getting nowhere fast! So, I decided
to go with the helper formula route.
 
Back
Top