Can I use arrayformula in Google Sheets to count things over a variable range?

Question

Is it possible to create a single arrayformula that does the same thing as the following column of formula:

A1: =COUNTA(B1:B1)
A2: =COUNTA(B1:B2)
A3: =COUNTA(B1:B3)
...
A9: =COUNTA(B1:B9)

My best try was =ARRAYFORMULA(COUNTA(INDIRECT("B$1:"&B1:9)) but I get a circular dependency error - I presume because Arrayformula doesn't know which of the two possible ranges to expand over. I'd sort of need to tell it which array to expend and which not. Maybe there's a notation like "::" for that? Would be cool anyways.

Answer 1

Formula

=ArrayFormula(MMULT(TRANSPOSE((ROW(B1:B9)<=TRANSPOSE(ROW(B1:B9)))*ISTEXT(B1:B9)*1),ISTEXT(B1:B9)*1))

What you are looking to do is called "running total".

The above formula use arrays, TRANSPOSE, MMULT (matrix multiplication), ISTEXT and unity multiplication (to parse TRUE as 1 and FALSE as 0) instead creating a COUNTA formula chain.

The first argument of MMULT creates a variant of a lower triangular matrix (all the values above the main diagonal are zero). Instead of all lower values be 1, they are 1 if the corresponding values are a text values, and 0 if not.

NOTE: Not all the spreadsheet functions are able to handle an array as argument. INDIRECT is one of those functions.

Answer 2

Try this and expand it :

=COUNTA(INDIRECT("B1:B"&ROW(B1)))

Note :

=ArrayFormula(COUNTA(INDIRECT("B1:B"&ROW(B1:B6)))) 
is  
Arrayformula ( COUNTA ( =INDIRECT(B1:B{1;2;3;4;5;6}   )   )

=INDIRECT()
Returns a cell reference specified by a string.

Source