I'm trying to count longest and current streak from null or 1 values in google sheets.
It's going to be used in Data Studio, but I have to make calculation in Sheets.
I've tried a sultion from below post, but it's nor working for me and also I have null values not 0, and it has to stay this way (rows in sheet are being appended from google forms form, where I check whether I did a habit or not). Google Sheets - How to count streaks of non-zero numbers on one column
Please help if you can, thanks before
Here is the spreadsheet with some example data https://docs.google.com/spreadsheets/d/1GaaEJ24ERulPftYAILOuokY929HInkh-SjAJUEvrW5M/edit?usp=sharing
Use this to get the longest streak in data:
=arrayformula(
if(
len(join(""; Data!A1:A));
max( len( split(
concatenate( left(isblank(Data!A1:A)) );
"T"
) ) );
"no streak"
)
)
And this to get the last streak in the data:
=+sort(
if(
len(join(""; Data!A1:A));
len( transpose( split(
concatenate( left(isblank(Data!A1:A)) );
"T"
) ) );
"no streak"
);
sequence( rows(
transpose( split(
concatenate( left(isblank(Data!A1:A)) );
"T"
) )
) );
false
)
See your sample spreadsheet.