CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulascumulative-sum
Petar Atanasov
Petar Atanasov

Reputation: 21

Cumulative count of working days with conditions (multiple employees) in Google Sheets

guys,

I have the following problem: I have the schedule of four employees and I need to count how many consecutive days they are working.

The file is in Google Sheets, here is the link, if someone wants to copy the data and try it: https://docs.google.com/spreadsheets/d/1TUJr9skcXQDtGEV36JVHMGgiLzPlCiEgaPqJ5ZazfFI/edit?usp=sharing

Here is the snip of the document:

Snipshot

In description, Col A, B and C are containing the date, name of the person and its schedule ("Off" for the days he\she doesn't work). This is the raw data.

Col D is converting to 0s if the person is Off and 1 if the person is working.

Col E is the column on which I can make changes, taking people off shift when they are scheduled and vice versa. (data validation column with 2 values).

Col F is converting to 0s if the person is Off and 1 if the person is working after my change.

In Col G I have used formula =IF(F2=0,0,SUMIFS(F$2:F,B$2:B,B2,A$2:A,"<="&A2)) to count the consecutive days, but unfortunately it counts all days with a casual zeros in between.

What I need is the numbers to restart after a zero (col H, I have filled it manually).

This is an example data with four employees, but my original data is with over 300.

I also am not very proficient in using scripts so I would really prefer to be a formula.

Any help would be highly appreciated!

P.S. If someone can suggest a better display of my data and how to achieve it I would be very grateful!

Upvotes: 1

Views: 497

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

This is just a pull-down formula: no auto-expanding formula so far I'm afraid:

=ArrayFormula(if(F2=0,0,
countifs(F$2:F2,1,B$2:B2,B2)-
countifs(F$1:F1,1,B$1:B1,B2,row(A$1:A1),"<"&iferror(vlookup(row(B2),if((B$1:B1=B2)*(F$1:F1=0),row(B$1:B1)),1),0))))

in (say) I2.

enter image description here

EDIT

This would have been a bit simpler:

=ArrayFormula(if(F2=0,0,
countifs(F$2:F2,1,B$2:B2,B2)-
countifs(F$1:F1,1,B$1:B1,B2,row(A$1:A1),"<"&max(if((B$1:B1=B2)*(F$1:F1=0),row(B$1:B1))))))

Upvotes: 1

basic
basic

Reputation: 11968

Another option with SUMPRODUCT:

=SUMPRODUCT((B2=$B$1:B2)*(ROW($B$1:B2)>IFERROR(LOOKUP(9^9,ROW($B$1:B2)/((B2=$B$1:B2)*(0=$F$1:F2))),1)))

enter image description here

Upvotes: 1

Related Questions