Counta ignore hidden columns

[JSmith1504]

Hi

I need a formula which counts non blanks.

My data range to count is H12:PG12 but some columns (which will include non blanks) are hidden.

I need to count the non blanks but NOT include the hidden columns in my count.

Any suggestions?

Many thanks

Jack

[ragulduy]

I don't think that is very easy with formulas. You can use the subtotal function to avoid hidden cells but that only works going down (i.e. avoiding hidden rows), not going across (avoiding hidden columns).

So I would think your options are:
-Transpose your data to go down instead of across
-Use a VBA solution like:

Please Login or Register  to view this content.

Press alt+f11 to open vba editor, click insert->module and copy and paste that code in, then in a cell on your spreadsheet put:
=sum_visible(H12:PG12)

edit: you will need to force recalculate using F9, although you could add something like:
=sum_visible(H12:PG12)+now()*0
but I still think it won't recalculate when unhiding a column. Only once you change a cell.

edit 2:
Having looked at google, the excellent PGC has previously suggested using =cell("width",A1) to return the width of the column and testing if this is above 0, so if you created a helper row with the cell widths and then used an if statement to remove the ones that were very small. Although you would still need to manually recalculate with this method.