Summing Visible Cells Only??

[Wilson]

Sorry if this duplicates, having a few malfunctions here. Is it possible to
write a SUMIF worksheet formula to sum visible cells in a range only, or
must this be done with code?
Windows 2000, Excel 2000 SR1, TIA

[Wilson]

Is it possible to write a SUMIF worksheet formula to sum visible cells only,

or must this be done with code?
Windows 2000, Excel 2000 SR1, TIA

--
Who Coded Thiง Meงง?

[Jim Rech]

If cells are hidden because you're using a filter then use the SUBTOTAL
worksheet function. If it's just a case of rows being hidden I think you
have to use a UDF.

--
Jim Rech
Excel MVP

[John Walkenbach]

If the cells are hidden as a result of AutoFiltering, you can use the SUBTOTAL function, with 9 as the first argument (see online help for details).

If the cells are hidden by manually hiding rows/columns, you'll need a custom worksheet function. Here's one for you:

Function SUMVISIBLE(ParamArray number())
' Returns the sum of visible cells in a range
Dim Cell As Range, i As Long
Application.Volatile
SUMVISIBLE = 0
For i = 0 To UBound(number)
If Not IsError(number(i)) Then
If TypeName(number(i)) = "Range" Then
Set number(i) = Intersect(number(i).Parent.UsedRange, number(i))
For Each Cell In number(i)
If IsError(Cell) Then SUMVISIBLE = Cell: Exit Function
If Not Cell.EntireRow.Hidden And Not Cell.EntireColumn.Hidden Then _
SUMVISIBLE = SUMVISIBLE + Evaluate(Application.Sum(Cell))
Next Cell
Else
SUMVISIBLE = SUMVISIBLE + Evaluate(Application.Sum(number(i)))
End If
End If
Next i
End Function

Just copy this function to a standard VBA module. Then, you can use it in your formulas. It works just like the SUM function, and supports multiple arguments.

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

"Wilson" <jwi...@wickes.com> wrote in message news:eBqporUlBHA.2156@tkmsftngp05...

> Who Coded Thi§ Me§§?
>
>

[Wilson]

Thank you, Jim. Can you give an assist with the User Defined Function? A
bit rusty right now and the user is in a big rush. TIA
"Jim Rech" <jar...@kpmg.com> wrote in message
news:#73OJzUlBHA.1644@tkmsftngp05...

[John Walkenbach]

One more thing... If you use the SUMVISIBLE function, be aware that it will not recalculate when rows or columns are hidden or unhidden. You'll need to force a recalc by pressing F9. Or, changing the contents of any cell will force a recalc.

-John

"John Walkenbach" <jo...@j-walk.com> wrote in message news:eYstK1UlBHA.1976@tkmsftngp05...

[Wilson]

Thanks very much, Mr. Walkenbach!

"John Walkenbach" <jo...@j-walk.com> wrote in message
news:eYstK1UlBHA.1976@tkmsftngp05...

> Who Coded Thiง Meงง?
>
>

[Jim Rech]

This should do it. Note that even when calc mode is automatic Excel will
not do a recalc when a row is hidden or unhidden. You'd have to trigger it
with an F9 after hiding/unhiding a row, or by some other change like
entering data that causes a recalc.

Function SumVis(Rg As Range) As Double
Dim Cell As Range
Application.Volatile
For Each Cell In Rg
If Cell.EntireRow.Hidden = False Then
''If only rows can be hidden remove this If
If Cell.EntireColumn.Hidden = False Then
SumVis = SumVis + Cell.Value
End If ''and this Enf If
End If
Next
End Function

Jim

[Eric Reuschling]

I work for a Civil engineering company, and we are trying to create a
"custom" number format. We want to convert decimal numbers to a feet/inches
format.

For example: If you have 14.70833, we want to format the cell so that it
will read 14'-8.5" or 14'-8 1/2" (including the feet and inch symbols). We
also need to be able to use this number in formulas, so it has to remain
accessible to Excel as a numeral.

Do you have any suggestions?

[Jimmy L. Day]

See my reply in .functions. Please post to only one group, as the same people
see all the groups and become frustrated after spending time trying to help then
see you have already gotten help in another group.