Counting consecutive values

[kyddrivers]

I am trying to figure out a way to count the days worked in between days off in a 2 week period.

I have a column for each of the days of the week, in those columns reflect the start time if they are working or Off if they are off for that day.

The formula I tried in column S is: =MAX(FREQUENCY(IF(E2:R2<>"off", MATCH("~"&E2:R2, E2:R2&"",0)), ROW(E2:R2)))

Column T is the value that I need.

[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD="align: center"]COL E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[/TR]
[TR]
[TD="align: center"]Sun[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]Sat[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]Sat[/TD]
[TD="align: center"]Values[/TD]
[TD="align: center"]Should Be[/TD]
[/TR]
[TR]
[TD="align: center"]off[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
[/TR]
[TR]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]off[/TD]
[TD="align: center"]off[/TD]
[TD="align: center"]1:00 AM[/TD]
[TD="align: center"]1:00 AM[/TD]
[TD="align: center"]1:00 AM[/TD]
[TD="align: center"]12:00 AM[/TD]
[TD="align: center"]2:00 AM[/TD]
[TD="align: center"]Off[/TD]
[TD="align: center"]Off[/TD]
[TD="align: center"]2:00 AM[/TD]
[TD="align: center"]2:00 AM[/TD]
[TD="align: center"]2:30 AM[/TD]
[TD="align: center"]2:00 AM[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]

Any help that can be provided is greatly appreciated as I have over 500 lines I need to do this calculation on.

Thanks in advance.

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=MAX(FREQUENCY(IF(ISNUMBER(E2:R2),COLUMN(E2:R2)),IF(E2:R2="off",COLUMN(E2:R2))))

 

[kyddrivers]

Thank you sooo much. You just saved me a ton of work

 

[Aladin Akyurek]

Thank you sooo much. You just saved me a ton of work

You are welcome.

 

[swikp]

In kyddrivers example, how would you total the number of times a cell is not blank and count consecutive non-blank cells as 1? Using the last row as an example and assumed that all values of "off" were replaced by a blank, I would want a total count=3, since E3 is not blank, H3-L3 is not blank, and O3-R3 is not blank.

 

[Aladin Akyurek]

In kyddrivers example, how would you total the number of times a cell is not blank and count consecutive non-blank cells as 1? Using the last row as an example and assumed that all values of "off" were replaced by a blank, I would want a total count=3, since E3 is not blank, H3-L3 is not blank, and O3-R3 is not blank.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(E3:R3),COLUMN(E3:R3)),IF(E3:R3="",COLUMN(E3:R3))),1))

 

[swikp]

As you expected, it worked beautifully! Thank you. As a follow-up, can this formula remain active if applied to a pivot table?

 

[Aladin Akyurek]

As you expected, it worked beautifully! Thank you.

You are welcome.

As a follow-up, can this formula remain active if applied to a pivot table?

I guess the formula is referencing ranges of a pivot table. As such, I don't see a problem with that.

 

[swikp]

Thank you Aladin for your helpful responses. In selecting various filters on my pivot table, the calculations based on the formula is shifted to the far right. Can this be moved to the left based on the filters or is it because of the reference ranges? Another thought is...if it's possible to do the calculation after the filters are selected.

 

[Aladin Akyurek]

Thank you Aladin for your helpful responses. In selecting various filters on my pivot table, the calculations based on the formula is shifted to the far right. Can this be moved to the left based on the filters or is it because of the reference ranges? Another thought is...if it's possible to do the calculation after the filters are selected.

It's hard to assess the situation without having the pivot table, I'm afraid.