Subtoal excluding hidden columns

[Trevor]

Does anyone how I can sum many columns within a row but exclude hidden colums
from the calculation?
The subtotal(109,"range") works for rows but not columns

Thank you!

 

[Michael]

The SUBTOTAL function is designed for columns of data, or vertical ranges. It
is not designed for rows of data, or horizontal ranges. For example, when you
subtotal a horizontal range using a function_num of 101 or greater, such as
SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But,
hiding a row in a subtotal of a vertical range does affect the subtotal.

 

[Peo Sjoblom]

You would need a vba solution, subtotal when it comes to hidden columns does
not work,
only rows will work. Also note that it won't work with hidden rows in
previous versions
to 2003.

--


Regards,


Peo Sjoblom

 

[xlmate]

I have tried the Subtotal function with a hidden column
and it work for me.

check the column headings which you want to apply subtotals to
under Add subtotal to in the Subtotal dialog box

Is this waht you are after?
Pls click Yes if this help

cheers

 

[Teethless mama]

=SUM(A1,C1,F1,H1)

adjust to suit

 

[xlmate]

If you are looking to sum across the columns, the only solution I know of is
a macro, try this UDF:

Function SUMVIS(rng)
Dim CellSum As Long
Dim Cell As Range
Application.Volatile
CellSum = 0
Set rng = Intersect(rng.Parent.UsedRange, rng)
For Each Cell In rng
If IsNumeric(Cell) Then
If Not Cell.EntireRow.Hidden And _
Not Cell.EntireColumn.Hidden Then _
CellSum = CellSum + Cell
End If
Next Cell
SUMVIS = CellSum
End Function

Enter : =SUMVIS(..your columns...)


Does this do what you want?
Pls click Yes if this have help you.

cheers,

 

[mcescher]

Does anyone how I can sum many columns within a row but exclude hidden colums
from the calculation?
 The subtotal(109,"range") works for rows but not columns

Thank you!

Here's a sum and a count function. Hope it helps. Chris M.

Function Sum_Visible_Cells(Cells_To_Sum As Object)
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function

Function Count_Visible_Cells(Cells_To_Count As Object)
Application.Volatile
For Each cell In Cells_To_Count
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
rowcnt = rowcnt + 1
End If
End If
Next
Count_Visible_Cells = rowcnt
End Function

 

[Shane Devenshire]

Very cute!

Shaen

=SUM(A1,C1,F1,H1)

adjust to suit