2

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.

2 Answers 2

2

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.

1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.