Search code examples
google-sheetsarray-formulascounting

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

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

Solution

  • 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))))