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

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.

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.

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

• This is wrong: "in this case because `"B1:B"&ROW(B1)` is already a range", that is a text value, not a range.
– Rubén
Sep 23, 2019 at 18:42
• Ok I edited it !
– pjmg
Sep 23, 2019 at 19:43
• IMHO the note isn't clear as `Arrayformula ( COUNTA ( =INDIRECT(B1:B{1;2;3;4;5;6} ) )` doesn't use the Google Sheets formula syntax. I think that you should explain it.
– Rubén
Sep 23, 2019 at 19:47