I have this table :


enter image description here

Basically i want to do counta() per row but using arrayformula(). The basic formula i use in A2 :



1 Answer 1


There are many ways to do that, but try a query smush to get started:

=arrayformula( len( substitute( transpose( query( transpose( left(B2:Z, 1) ), "", 9^9 ) ), " ", "" ) ) )

There are more than 50,000 blank rows in your sample spreadsheet. You may want to delete them before you apply the formula.

  • what does that 9^9 actually do in the query() ?
    – andio
    Commented Jul 20, 2021 at 18:41
  • See query smush. Commented Jul 20, 2021 at 22:03

Your Answer

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

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