Looking to use ARRAYFORMULA to increment-count adjacent rows containing either TRUE or FALSE

user_16394056 0
Viewed 164

I have a dynamic table in Google Sheets that has, among other irrelevant columns, column B that has done various calculations and resulted in either TRUE or FALSE for each row in B4:B500 (the first three rows are headers and summaries).

What I would like is to be able to calculate in another column (C would be good!) how many TRUEs there have been so far (top down) in the current streak and, when B changes to FALSE, do the same thing, resetting back to 1 at each change in B's value.

Here is a link to example of my data (sorry, rep<10 so can't just post the image): sample data

Since the actual data is a lot more than ~20 rows and will be updated at least once per day for the foreseeable future, I'd prefer to use ARRAYFORMULA to calculate C rather than having to drag formulae down. Additionally, unless the scripting is extraordinarily simplified, I have a very strong preference for formulae rather than scripts.

If I wanted all of the TRUEs or all of the FALSEs (or even all within a pre-determined range), I could do that already; it's the dynamic nature of the problem that is stumping me.

advTHANKSance

array-formulas counting google-sheets
4 Answers

Clear C4:C500, then place the following in cell C4:

=ArrayFormula(IF(B4:B500="",, ROW(B4:B500) - VLOOKUP(ROW(B4:B500), FILTER(ROW(B4:B500), B4:B500<>"", B4:B500<>B3:B499), 1, TRUE) + 1))

The FILTER creates a list of all row numbers corresponding with non-null cells from B4:B where the current cell doesn't have the same value as the previous cell. This list will contain only the rows that "restart" a new value.

VLOOKUP with a final parameter of TRUE will lookup every row number corresponding with B4:B500 within that FILTERed short list above and "fall back" to the nearest value. So if the FILTERed list starts 4, 8, 9... then row 5 will return 4, row 6 will return 4, etc. Subtracting that most recent fall-back row number from every actual row number will product the count from the last change.

We add +1 because the count at each change point starts at 1 not at 0. For instance, for row 4, we'd get 4 (current row) - 4 (fall back value) = 0; but we want row 4 to start the count at 1, hence the +1 to each value.

user_139032830

With 2 columns ... (without specifying the end of the column)

in C2

=arrayformula(B4:B&(SUMIF(ROW(B4:B),"<="&ROW(B4:B),C4:C)))

in D2

=arrayformula((row(B4:B)<=transpose(row(B4:B)))*((B4:B&(SUMIF(ROW(B4:B),"<="&ROW(B4:B),C4:C)))=TRANSPOSE((B4:B&(SUMIF(ROW(B4:B),"<="&ROW(B4:B),C4:C))))))

enter image description here

user_150193800

EDIT: A much more concise formula which uses a similar logic:

=index(if(B4:B="",,len(RegexReplace(left(join(,left(B4:B)),row(B4:B)-3),".*"&if(B4:B,"F","T"),))))

Here's a different approach with Regex.

=ArrayFormula(IFNA(len(RegexExtract(RegexReplace(RegexReplace(join(,B4:B500),"RUE|ALSE",),"^(.{"&sequence(counta(B4:B500))&"})","$1~"),"(.*)~"))-len(RegexExtract(RegexReplace(RegexReplace(join(,B4:B500),"RUE|ALSE",),"^(.{"&sequence(counta(B4:B500))&"})","$1~"),"(.*"&if(RegexExtract(RegexReplace(RegexReplace(join(,B4:B500),"RUE|ALSE",),"^(.{"&sequence(counta(B4:B500))&"})","$1~"),"(.)~")="T","F","T")&").+~")),sequence(999)))

enter image description here

It's not the best solution both in terms of length and versatility as it's only designed to work with this specific input (TRUE/FALSE) but it was an interesting problem so I wanted to give it a try.

user_178873010

Another one to try:

=ArrayFormula(if(B2:B="",,row(B2:B)-
if(B2:B,
iferror(vlookup(row(B2:B),if(not(B2:B),row(B2:B)),1),1),
iferror(vlookup(row(B2:B),if(B2:B,row(B2:B)),1),1))))

enter image description here

A more general expression for the iferror value if you want to start in row 4 etc:

=ArrayFormula(if(B4:B="",,row(B4:B)-
if(B4:B,
iferror(vlookup(row(B4:B),if(not(B4:B),row(B4:B)),1),min(row(B4:B))-1),
iferror(vlookup(row(B4:B),if(B4:B,row(B4:B)),1),min(row(B4:B))-1))))

or for your specific requirements:

=ArrayFormula(if(B4:B="",,row(B4:B)-
if(B4:B,
iferror(vlookup(row(B4:B),if(not(B4:B),row(B4:B)),1),3),
iferror(vlookup(row(B4:B),if(B4:B,row(B4:B)),1),3))))
user_38949170
Related