I am trying to count all blank cells in a row while ignoring hidden columns but I can't find any formula that returns the right answer. The SUBTOTAL function only works on hidden rows but I cannot change my data to hide rows instead of columns.

For example, I wan to count blank cells from B2:BA2 but need to ignore any blank cells from hidden columns between that range.

Appreciate any help!

CodePudding user response：

You can try the following VBA function:

Function CntBlnk(Rng As Range)
Dim Cell As Range
Application.Volatile
For Each Cell In Rng

If Cell.EntireColumn.Hidden = False And Len(Trim(Cell)) = 0 Then

CntBlnk = CntBlnk   1
End If
Next Cell
End Function

Then call the function CntBlnk in the required cell.

CodePudding user response：

A VBA solution is probably the best option here. A set-up using worksheet formulas alone is possible, viz:

=SUMPRODUCT(N(CELL("width",OFFSET(B2,,COLUMN(B2:BA2)-MIN(COLUMN(B2:BA2))))>0),N(B2:BA2=""))

or, Office 365:

=SUMPRODUCT(N(CELL("width",OFFSET(B2,,SEQUENCE(,COLUMNS(B2:BA2),0)))<>0),N(B2:BA2=""))

though it suffers three drawbacks:

1. It's volatile
2. Despite said volatility, changes to the column widths in the range passed will not trigger a recalculation of this formula; the user will need to perform a manual recalculation
3. Columns having a column width of less than 0.5 will be treated as hidden

CodePudding user response：

If you have Excel 365 and are open to using a Lambda, you could also try:

=LAMBDA(ref,i,IFERROR(ISBLANK(INDEX(ref,i))*(@CELL("width",INDEX(ref,i))>0),chb(ref,i 1)))

where the Lambda is named as chb in the name manager.

As with the previous answer, it suffers from the problem that Cell doesn't update until you force the sheet to re-calculate.