CODEWITHSUNDEEP
excel
ScottCee
ScottCee

Reputation: 179

Counting the max number of times a value occurs consecutively

I have a list of users with the number of times they have been file checked in a 12 month period. I want to identify (in column H) how many consecutive months there has been NO file check for that user. Eg :

   A      B    C     D      E     F      G
User    |Oct |Nov | Dec  | Jan | Feb  | Mar
A       | 0  | 1  | 1    | 0   | 0    | 0 
B       | 1  | 1  | 0    | 0   | 1    | 0 
C       | 0  | 0  | 1    | 0   | 0    | 0
D       | 2  | 0  | 0    | 0   | 1    | 1

I know a simple COUNTIF would give me the total number of 0's for each user, but I want to calculate how many consecutive months and, where there has been more than one 'block' of consecutive 0's, what the longest period is.

Upvotes: 3

Views: 302

Answers (1)

jsheeran
jsheeran

Reputation: 3037

Inelegant, but it appears to work:

=MAX(IF(IFERROR(FIND(REPT("0",ROW($1:$6)),CONCAT($B2:$G2)),0),ROW($1:$6),0)) [Ctrl+Shift+Enter]

It iterates through the numbers yielded by ROW($1:$6) to find the maximum number of zeroes in the concatenation of your per-month values in each row. Enter it as an array formula into H2 and fill down.

Upvotes: 1

Related Questions