Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtoal excluding hidden columns
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtoal excluding hidden columns
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. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Trevor" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtoal excluding hidden columns
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 "Trevor" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtoal excluding hidden columns
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 "Trevor" wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtoal excluding hidden columns
=SUM(A1,C1,F1,H1)
adjust to suit "Trevor" wrote: 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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtoal excluding hidden columns
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, "xlmate" wrote: 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 "Trevor" wrote: 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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtoal excluding hidden columns
On Nov 25, 8:55*am, Trevor wrote:
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtoal excluding hidden columns
Very cute!
Shaen "Teethless mama" wrote: =SUM(A1,C1,F1,H1) adjust to suit "Trevor" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif excluding hidden rows | Excel Worksheet Functions | |||
Excluding formulas in hidden cells? | Excel Worksheet Functions | |||
Sum of a column excluding hidden rows | Excel Worksheet Functions | |||
Need to sum columns, excluding hidden columns. | Excel Discussion (Misc queries) | |||
Need to sum columns, excluding hidden ones - like 'subtotal' for r | Excel Discussion (Misc queries) |