horiziontal subtotal visible cells only

Hi guys,

If I use :

subtotal(109, "A1:A10") then it will only sum the visible rows

However, if I do
subtotal(109, "A1:H1") then it ignores the visible or hidden columns are all counted....

How can I do a subtotal horizontal on columns, ignoring the cells in hidden columns?

Thanks!

Your best bet would be to use a User Defined Function that include volatility, so that it updates dynamically.

Copy the code below, then in Excel hit Alt+F11. Go to Insert -> Module, and paste the code in there, and close that window.

Now you have a new Function called SumVisible. Now =SumVisible(A1:H1) does what you need.

Please Login or Register  to view this content.

Or, if you can guarantee that no visible columns will have a width less than 0.5, you could put, in A2:

=CELL("width",A1)

and copy to the right. Then:

=SUMIF(A2:H2,">=0.5",A1:H1)

Regards

Thanks guys, I used the custom function method

Wooops...

application.volatile doesn't respond to hiding or showing columns...
How to trigger that?

Hit F9 maybe?