Summing non hidden values in a range

[starguy]

I have data in a range of D11:K11 and its sum in cell L11. This goe
down to around 30 rows. Sometimes I have to hide columns in range o
D11:K11 and want to sum the non hidden cells in L11 and down to 3
rows.
Is there any formula or function??

 

[Biff]

Hi!

What version of Excel are you using?

If you're using Excel 2003:

=SUBTOTAL(109,D11:K11)

If you're using any other version I think you may need a UDF.

Biff

 

[CaptainQuattro]

I have data in a range of D11:K11 and its sum in cell L11. This goes
down to around 30 rows. Sometimes I have to hide columns in range of
D11:K11 and want to sum the non hidden cells in L11 and down to 30
rows.
Is there any formula or function???

Try =SUBTOTAL(9,D11:K11)

 

[JB]

With UDF (Excel <2003)


Function sumVisibles(champ As Range)
Application.Volatile
t = 0
For Each c In champ
If c.EntireColumn.Hidden = False Then t = t + c.Value
Next c
sumVisibles = t
End Function


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
calcultate
End Sub

Cordialy JB

 

[starguy]

using Excel 2003 but it did not work.

 

[starguy]

thanks for reply but I dont know to implement UDF because I dont know
VB.
I m using Excel 2003 then why this function does not work???

 

[Bob Phillips]

How have you hidden the rows?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[starguy]

I have hidden columns not rows. I hide them by both ways using Ctrl+9
and by menu Format > Column > Hide but nothing happened after hiding. I
also pressed F9 to recalculate after hiding columns.

 

[Bob Phillips]

Something like =SUBTOTAL(109,A1:E1) should work in those circumstances.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[starguy]

its not working. i m sending you sample workbook. please check what i
the problem

+-------------------------------------------------------------------
|Filename: Book1.zip
|Download: http://www.excelforum.com/attachment.php?postid=4714
+-------------------------------------------------------------------

 

[Bob Phillips]

Afraid I can't, I don't have 2003.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[starguy]

what should I do. I need it... any body esle...

 

[Ardus Petus]

I don't have XL 2003 either..

Sounds like you need an UDF.

Here is some code you can paste in a Module

'------
Function TOTAL_VISIBLE(rng As Range) As Long
Dim c As Range
For Each c In rng
With c
If Not .EntireColumn.Hidden Then
TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
End If
End With
Next c
End Function
'---------

 

[Bob Phillips]

Problem with a UDF is that it is not recalculated if a new row gets
hidden/unhidden. You would have to force it somehow.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

=subtotal(109,....) works nicely with hidden rows--not so nicely with hidden
columns in xl2003.

 

[Bob Phillips]

Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works fine with
columns?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

=subtotal(9,a1:e1)
didn't exclude cells in hidden columns for me in xl2003.

(Did you really mean to type columns in your question?)

 

[Bob Phillips]

I am not sure what I was thinking now, as it couldn't have worked. You can't
filter a row, so if anything in A1:E1 is filtered, it all is. Oh well. Poor
implementation though, they obviously just carried the existing
functionality, without considering that whilst columns may not get hidden by
filter, they can by manually hiding.

Bob

 

[Dave Peterson]

It sure seems like a small step (to me, anyway) to make =subtotal(1##,...) work
with hidden columns.

But who the heck knows, well outside of MS?

I am not sure what I was thinking now, as it couldn't have worked. You can't
filter a row, so if anything in A1:E1 is filtered, it all is. Oh well. Poor
implementation though, they obviously just carried the existing
functionality, without considering that whilst columns may not get hidden by
filter, they can by manually hiding.

Bob

 

[Biff]

That'll be a new feature in Excel 14.

Biff

It sure seems like a small step (to me, anyway) to make =subtotal(1##,...)
work
with hidden columns.

But who the heck knows, well outside of MS?