Count Number of Times String Occurs within a Time Period

[fixthis]

I am in need of a solution to count the number of times the word “Strip” and "Prep" appears in a column but stop when the cumulative time in another column reaches 24 hours (Elapsed Time hrs column).


Data is set up like this:

Start Stop Mode Elapsed Time (min) Elapsed Time (hrs)
12:00am 1:00am Prep 1 1
1:00am 2:00am Strip 1 1
2:00am 3:00am Prep 1 1

(see attached file)

So for when Strip time is 1 hour and Prep time is 1 hour, then starting at midnight, the number of Strip periods would be 12 (to the next midnight). However if I change the prep times to 15 minutes or 0.25 hour, and leave the Strip times at 1 hour, then the table would change and number of Strip times in 24 hours would be 19 and the number of Prep time periods is 20.

Right now I manually change the range to match 24 hours worth of rows using the following formulas:

=COUNTIF($D$4:$D$42,"*Strip*")

=COUNTIF($D$4:$D$42,"*Prep*")

It would be nice to have the formula to account for the time period automatically.

As a bonus, I would like the table rows to be highlighted for those rows being counted within the twenty four hours. Using Excel 2010.

 

[xlstime]

Hey fixthis,

Please see if this is OK.

There is another way to do this using array formula (without using helper column)

I am in need of a solution to count the number of times the word “Strip” and "Prep" appears in a column but stop when the cumulative time in another column reaches 24 hours (Elapsed Time hrs column).


Data is set up like this:

Start Stop Mode Elapsed Time (min) Elapsed Time (hrs)
12:00am 1:00am Prep 1 1
1:00am 2:00am Strip 1 1
2:00am 3:00am Prep 1 1

(see attached file)

So for when Strip time is 1 hour and Prep time is 1 hour, then starting at midnight, the number of Strip periods would be 12 (to the next midnight). However if I change the prep times to 15 minutes or 0.25 hour, and leave the Strip times at 1 hour, then the table would change and number of Strip times in 24 hours would be 19 and the number of Prep time periods is 20.

Right now I manually change the range to match 24 hours worth of rows using the following formulas:

=COUNTIF($D$4:$D$42,"*Strip*")

=COUNTIF($D$4:$D$42,"*Prep*")

It would be nice to have the formula to account for the time period automatically.

As a bonus, I would like the table rows to be highlighted for those rows being counted within the twenty four hours. Using Excel 2010.

 

[fixthis]

Hi xlstime,

Thank you for your assistance as it is a solution that works if I change F46 to just 24. However, it looks like you did use a helper column and the SUMPRODUCT formula is not an array formula (opposite of what you stated).

I am wondering if there is a non-helper column solution that can do the same thing as I am not fond of helper columns. I also would need to move the summary data at the bottom of the table to the right and expand the table down to capture differing scenarios, especially as the prep nd strip times decrease.

 

[bosco_yip]

Maybe………

1] "Cumulative hours" F46 , enter "24"

2] "Part" B46 &B47, enter "Strip" and "Prep"

3] In D46, formula copy down :

=SUMPRODUCT((D$3:D$42=B46)*(SUMIF(OFFSET(F$3,,,ROW($1:$40),),"<>")<=F$46))

4] "Cumulative hours" can be changed into other time.

Regards
Bosco