CODEWITHSUNDEEP
google-sheetsarray-formulas
Comfort Eagle
Comfort Eagle

Reputation: 2462

Count blanks row by row

I need an auto-expanding formula to count blank cells in each row, as long as there are values in B. I've tried =ARRAYFORMULA(IF(ISBLANK(B2:B),IFERROR(1/0),COUNTBLANK(E2:2))) and thereby managed the expansion, but it keeps counting E2:2 and not the actual row the array expands to.

Upvotes: 1

Views: 326

Answers (2)

Wicket
Wicket

Reputation: 38254

Using INDIRECT to add auto-adjustable references to the formula from the answer by @JPV

=ArrayFormula(
  IF(
    LEN(INDIRECT("B2:B"&COUNT(B2:B)+1)),
    MMULT(N(ISBLANK(INDIRECT("B2:"&COUNT(B2:B)+1))), TRANSPOSE(column(B2:2)^0)),
  )
)

NOTE: It's assumed that the values in B2:B are numbers or blanks.

Upvotes: 2

JPV
JPV

Reputation: 27262

Try something like this

=ArrayFormula(IF(LEN(B2:B100),MMULT(N(ISBLANK(B2:100)), TRANSPOSE(column(B2:2)^0)),))

Change range to suit.

Upvotes: 2

Related Questions