google-sheetsarray-formulas

Counting blank cells by arrayformula


I would like an arrayformula in B1 that calculates for every cell in column B how many empty cells I have in the previous rows in column A, including the current row. I wasn't able to achieve this even with ChatGPT or Claude Sonnet...


Solution

  • Use scan(), like this:

    =scan(0, A1:A, lambda(a, c, a + isblank(c)))
    

    See scan().