I need a formula to dynamically compute the number of empty Columns from the datasets.

Basically I would need the same as the vertical dynamic end row limit but for column.

For example I would need =COUNTA(A1:A) but for colums for example =COUNTA(A1:BZ) but without absolute reference to the column.

I have multiple datasets with a varying number of columns. Each dataset also has a varying number of empty columns.

For example:

I have the followwing formula in Cell A1 as of now:

=SUM(COUNTIF(B2:K,"<1"))

With =COUNTA(B2:B) dragged to column J/ Row J2 as so:

I don't know if there's a way to set the Horizontal ending cell as dynamic and couldn't find another similar question on SO.

Thank you for sharing the solution if you know one.

CodePudding user response：

if lets say you wish to get the dynamic blank column count of the tab Sheet1; try:

=COUNTIF(BYCOL(INDIRECT("Sheet1!1:"&MAX(ROWS(Sheet1!A:A))),LAMBDA(z,COUNTA(z))),0)