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().