Counting empty cells in Google Sheet-CodePudding

I have a Google sheet like this: spreadsheet and I am trying to figure out if in column O it is possible to check if there is a value in a cell and then count all empty cells before it in the row? So for example O2 should say 3 and O3 should 5.

I have been trying using =COUNTBLANK for the row, but can't figure out a way to only count backwards from the first value (marked in yellow on the screenshot).

CodePudding user response：

Another solution (in O2)

=BYROW(A2:N,LAMBDA(r,IFERROR(1/COUNTBLANK(
FILTER(r,COLUMN(r)<MIN(IF(r<>"",COLUMN(r)))))^-1)))

CodePudding user response：

Try this in 02 (delete any values below it because it will expand):

=byrow(map(B2:N,lambda(n,if(ISBLANK(n),"",1))),lambda(each,IFERROR(MATCH(1,each,0)-1,"")))