CODEWITHSUNDEEP
excelcounthiddensubtotal
Mina
Mina

Reputation: 9

How to Count Blank excluding hidden columns in Excel

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!

Upvotes: 0

Views: 918

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34230

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

=LAMBDA(range,index,IF(index>COLUMNS(range),0,ISBLANK(INDEX(range,index))*(@CELL("width",INDEX(range,index))>0)+CountVisBlanks(range,index+1)))

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

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

Called as:

=CountVisBlanks(b2:ba2,1)

Upvotes: 0

Jos Woolley
Jos Woolley

Reputation: 9062

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

Upvotes: 2

Gurbir Singh
Gurbir Singh

Reputation: 41

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.

Upvotes: 3

Related Questions