I’m quite new to the forums, so I apologise if I haven’t formatted this correctly. I’ve had many (many) searches across the forums and found something that is almost working, however it’s not working quite how I need.
I’m needing a running total of how many times "Fail" appears consecutively, and then resetting when "Pass" appears. The data will be consistently updating. My current code is:
=ArrayFormula(IF(C1:C="",, COUNTIFS(C1:C, C1:C, ROW(C1:C), "<="&ROW(C1:C), ROW(C1:C), ">="&VLOOKUP(ROW(C1:C)-1, SORT(IF(C1:C="", 0, (VLOOKUP(ROW(C1:C)-1, {ROW(C:C), C:C}, 2, TRUE)<>C1:C)*ROW(C1:C))), 1, TRUE))))
However this is also counting consecutive "Pass" too.
Here is a small data example: https://docs.google.com/spreadsheets/d/1-9v6Qisjn1cpBbvlQCCw3y4l0cNuAgAx3p1jSS9JJac/edit?usp=sharing
Any help would be much appreciated. Thanks so much.
>Solution :
try:
=ARRAYFORMULA(IF(C1:C="",, IF(C1:C="Pass", 0,
COUNTIFS(C1:C, C1:C, ROW(C1:C), "<="&ROW(C1:C), ROW(C1:C), ">="&
VLOOKUP(ROW(C1:C)-1, SORT(IF(C1:C="", 0, (
VLOOKUP(ROW(C1:C)-1, {ROW(C:C), C:C}, 2, 1)<>C1:C)*ROW(C1:C))), 1, 1)))))
