google-sheets
Calculate the number of times groupings of three zeros (0's) appear in a row
I tried using the "Countifs" (Google Measure) ...but, I'm hoping there's a simple(r) way to "Look" 👀 ...at:
- rows of zeros (0's) ...and
- determine how many times three (3) zeros appear [together]
- see Google Sheet - below ⤵️
Explained:
- Employee A ...has 4 instances (over 18 weeks)
- Employee B ...has 2 instances (over 18 weeks)
- Employee C ...has 6 instances (over 18 weeks)
- Employee D ...has 4 instances (over 18 weeks)
Solution
Here's a more traditional method of counting consecutive occurrences of a certain character:
=ArrayFormula(byrow(B2:S,lambda(r,if(counta(r),let(isZero,r&""="0",cols,column(B2:S),sum(quotient(frequency(if(isZero,cols),if(not(isZero),cols)),3))),))))
where the column numbers of cells which don't contain a zero mark out the bin ranges, while the column numbers of cells which do contain a zero are the data to be counted.
The first time I became aware of this method was way back in 2015, in this answer which contains a more detailed explanation.