CODEWITHSUNDEEP
regexif-statementgoogle-sheetsfrequencyarray-formulas
ONM
ONM

Reputation: 61

Count consecutive days/occurrences based on multiple criteria

In the attached sheet, I have a problem with the below formula.

=IF(B2="","", ArrayFormula(max(frequency(if(E2:Z2=B2,row(E2:Z2)),if(E2:Z2<>B2,row(E2:Z2))))))

Located in Column D, it is attempting to count consecutive days based on the below criteria:

Column C is the expected answer.

The result simply counts all consecutive occurrence across the entire line, this may be in the future or they may have used a different asset for a single day which interrupts the consecutive count, however, it continues after if there are indeed more to count.

Highlighted Green is the range that I'm trying to count.

For reference, Please assume that Today() is 01/08/2019 else things would continually change.

https://docs.google.com/spreadsheets/d/1FCCnSqMmH5jLFLTQFnmLlIrkiIicjHFvTcP87zBBjPM/edit#gid=0

Upvotes: 1

Views: 391

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(LEN(REGEXEXTRACT(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE(IF(
 INDIRECT("E2:"&ADDRESS(COUNTA(B2:B)+1, MATCH(TODAY(), 1:1, 0), 4))=
 INDIRECT(ADDRESS(2, MATCH(TODAY(), 1:1, 0), 4)&":"&
          ADDRESS(COUNTA(B2:B)+1, MATCH(TODAY(), 1:1, 0), 4)), 1, "♦"))
 ,,999^99)), " ", ), "(\d+)$")))

0

Upvotes: 1

Related Questions