Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
starguy
 
Posts: n/a
Default Summing non hidden values in a range


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???


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953

  #2   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Summing non hidden values in a range

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

"starguy" wrote in
message ...

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???


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953



  #3   Report Post  
Posted to microsoft.public.excel.misc
CaptainQuattro
 
Posts: n/a
Default Summing non hidden values in a range


starguy Wrote:
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)


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=537953

  #4   Report Post  
Posted to microsoft.public.excel.misc
JB
 
Posts: n/a
Default Summing non hidden values in a range

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
starguy
 
Posts: n/a
Default Summing non hidden values in a range


using Excel 2003 but it did not work.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953



  #6   Report Post  
Posted to microsoft.public.excel.misc
starguy
 
Posts: n/a
Default Summing non hidden values in a range


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???


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953

  #7   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

How have you hidden the rows?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy" wrote in
message ...

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???


--
starguy
------------------------------------------------------------------------
starguy's Profile:

http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953



  #8   Report Post  
Posted to microsoft.public.excel.misc
starguy
 
Posts: n/a
Default Summing non hidden values in a range


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 Wrote:
How have you hidden the rows?

--
HTH

Bob Phillips



--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953

  #9   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy" wrote in
message ...

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 Wrote:
How have you hidden the rows?

--
HTH

Bob Phillips



--
starguy
------------------------------------------------------------------------
starguy's Profile:

http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953



  #10   Report Post  
Posted to microsoft.public.excel.misc
starguy
 
Posts: n/a
Default Summing non hidden values in a range


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


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

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953



  #11   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy" wrote in
message ...

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


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

--
starguy
------------------------------------------------------------------------
starguy's Profile:

http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953



  #12   Report Post  
Posted to microsoft.public.excel.misc
starguy
 
Posts: n/a
Default Summing non hidden values in a range


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


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953

  #13   Report Post  
Posted to microsoft.public.excel.misc
Ardus Petus
 
Posts: n/a
Default Summing non hidden values in a range

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
'---------

"starguy" a écrit dans
le message de news: ...

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


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953



  #14   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

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)

"Ardus Petus" wrote in message
...
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
'---------

"starguy" a écrit

dans
le message de news: ...

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


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:

http://www.excelforum.com/showthread...hreadid=537953





  #15   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

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

Bob Phillips wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy" wrote in
message ...

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 Wrote:
How have you hidden the rows?

--
HTH

Bob Phillips



--
starguy
------------------------------------------------------------------------
starguy's Profile:

http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953


--

Dave Peterson


  #16   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

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" wrote in message
...
=subtotal(109,....) works nicely with hidden rows--not so nicely with

hidden
columns in xl2003.

Bob Phillips wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy" wrote

in
message ...

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 Wrote:
How have you hidden the rows?

--
HTH

Bob Phillips


--
starguy


------------------------------------------------------------------------
starguy's Profile:

http://www.excelforum.com/member.php...o&userid=32434
View this thread:

http://www.excelforum.com/showthread...hreadid=537953


--

Dave Peterson



  #17   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

=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 wrote:

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" wrote in message
...
=subtotal(109,....) works nicely with hidden rows--not so nicely with

hidden
columns in xl2003.

Bob Phillips wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy" wrote

in
message ...

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 Wrote:
How have you hidden the rows?

--
HTH

Bob Phillips


--
starguy

------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:

http://www.excelforum.com/showthread...hreadid=537953


--

Dave Peterson


--

Dave Peterson
  #18   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

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" wrote in message
...
=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 wrote:

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" wrote in message
...
=subtotal(109,....) works nicely with hidden rows--not so nicely with

hidden
columns in xl2003.

Bob Phillips wrote:

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

circumstances.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy"

wrote
in
message ...

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 Wrote:
How have you hidden the rows?

--
HTH

Bob Phillips


--
starguy


------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:

http://www.excelforum.com/showthread...hreadid=537953


--

Dave Peterson


--

Dave Peterson



  #19   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

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?

Bob Phillips wrote:

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" wrote in message
...
=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 wrote:

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" wrote in message
...
=subtotal(109,....) works nicely with hidden rows--not so nicely with
hidden
columns in xl2003.

Bob Phillips wrote:

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

circumstances.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy"

wrote
in
message ...

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 Wrote:
How have you hidden the rows?

--
HTH

Bob Phillips


--
starguy


------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #20   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Summing non hidden values in a range

That'll be a new feature in Excel 14.

Biff

"Dave Peterson" wrote in message
...
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?

Bob Phillips wrote:

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" wrote in message
...
=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 wrote:

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" wrote in message
...
=subtotal(109,....) works nicely with hidden rows--not so nicely
with
hidden
columns in xl2003.

Bob Phillips wrote:

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

circumstances.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy"

wrote
in
message
...

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 Wrote:
How have you hidden the rows?

--
HTH

Bob Phillips


--
starguy


------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson





  #21   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

You're skipping excel 12 and excel 13????

O, ye of little faith.



Biff wrote:

That'll be a new feature in Excel 14.

Biff

<<snipped
  #22   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Summing non hidden values in a range

You're skipping excel 12 and excel 13????

12's already "in the box". I think they may be superstitious and skip 13 and
go right to 14.

Biff

"Dave Peterson" wrote in message
...
You're skipping excel 12 and excel 13????

O, ye of little faith.



Biff wrote:

That'll be a new feature in Excel 14.

Biff

<<snipped



  #23   Report Post  
Posted to microsoft.public.excel.misc
starguy
 
Posts: n/a
Default Summing non hidden values in a range


what should I do now...problem persists.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953

  #24   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Summing non hidden values in a range

Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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
'---------

"starguy" a écrit
dans le message de news:
...

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


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953





  #26   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Summing non hidden values in a range

Try JB's UDF.

But note that hidding/unhidding columns/rows does not trigger a calculation
so the formula will not update until a calculation is either automatically
triggered or you manually calculate by hitting function key F9.

Biff

"starguy" wrote in
message ...

what should I do now...problem persists.


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953



  #27   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Summing non hidden values in a range

Did you enter a valid range? (eg: =total_visible(A1:A4) )

Yes. I got it to return a sum but it doesn't change when columns are hidden
and I calculate.

I had changed the function name but didn't realize it was called later in
the procedure. I changed it back.

Biff

"Ardus Petus" wrote in message
...
Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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
'---------

"starguy" a écrit
dans le message de news:
...

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


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953









  #28   Report Post  
Posted to microsoft.public.excel.misc
CaptainQuattro
 
Posts: n/a
Default Summing non hidden values in a range


Here's a workaround that will work in any version of Excel:


In row 1 Column D enter =CELL("width",A2)

Copy to cells E1 through K1

in Cell L11 enter

=SUMIF(D1:K1,"0",D11:K11)


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=537953

  #29   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

Can you explain that, it doesn't work for me?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"CaptainQuattro"
<CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in
message news:CaptainQuattro.277voy_1146635100.8876@excelfo rum-nospam.com...

Here's a workaround that will work in any version of Excel:


In row 1 Column D enter =CELL("width",A2)

Copy to cells E1 through K1

in Cell L11 enter

=SUMIF(D1:K1,"0",D11:K11)


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile:

http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=537953



  #30   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

.....


This tells excel to recalculate the function whenever excel recalculates.

If I recall correctly, some versions of excel won't recalc when columns are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust the
results.



Biff wrote:

Did you enter a valid range? (eg: =total_visible(A1:A4) )


Yes. I got it to return a sum but it doesn't change when columns are hidden
and I calculate.

I had changed the function name but didn't realize it was called later in
the procedure. I changed it back.

Biff

"Ardus Petus" wrote in message
...
Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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
'---------

"starguy" a écrit
dans le message de news:
...

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


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953








--

Dave Peterson


  #31   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

.....


This tells excel to recalculate the function whenever excel recalculates.

If I recall correctly, some versions of excel won't recalc when columns are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust the
results.

starguy wrote:

what should I do now...problem persists.

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953


--

Dave Peterson
  #32   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

Still need to force a recalc, at least in XP you do.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

....


This tells excel to recalculate the function whenever excel recalculates.

If I recall correctly, some versions of excel won't recalc when columns

are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust the
results.



Biff wrote:

Did you enter a valid range? (eg: =total_visible(A1:A4) )


Yes. I got it to return a sum but it doesn't change when columns are

hidden
and I calculate.

I had changed the function name but didn't realize it was called later

in
the procedure. I changed it back.

Biff

"Ardus Petus" wrote in message
...
Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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
'---------

"starguy" a

écrit
dans le message de news:
...

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


--
starguy


-----------------------------------------------------------------------

-
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953








--

Dave Peterson



  #33   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

xl2003, too. But I don't recall if that ever changed from previous versions.

Bob Phillips wrote:

Still need to force a recalc, at least in XP you do.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

....


This tells excel to recalculate the function whenever excel recalculates.

If I recall correctly, some versions of excel won't recalc when columns

are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust the
results.



Biff wrote:

Did you enter a valid range? (eg: =total_visible(A1:A4) )

Yes. I got it to return a sum but it doesn't change when columns are

hidden
and I calculate.

I had changed the function name but didn't realize it was called later

in
the procedure. I changed it back.

Biff

"Ardus Petus" wrote in message
...
Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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
'---------

"starguy" a

écrit
dans le message de news:
...

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


--
starguy

-----------------------------------------------------------------------

-
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953








--

Dave Peterson


--

Dave Peterson
  #34   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

Nope, no good in 2000 or 97.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
xl2003, too. But I don't recall if that ever changed from previous

versions.

Bob Phillips wrote:

Still need to force a recalc, at least in XP you do.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

....


This tells excel to recalculate the function whenever excel

recalculates.

If I recall correctly, some versions of excel won't recalc when

columns
are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust

the
results.



Biff wrote:

Did you enter a valid range? (eg: =total_visible(A1:A4) )

Yes. I got it to return a sum but it doesn't change when columns are

hidden
and I calculate.

I had changed the function name but didn't realize it was called

later
in
the procedure. I changed it back.

Biff

"Ardus Petus" wrote in message
...
Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time

(with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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
'---------

"starguy"

a
écrit
dans le message de news:
...

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


--
starguy


-----------------------------------------------------------------------

-
starguy's Profile:

http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953








--

Dave Peterson


--

Dave Peterson



  #35   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

Thanks for testing.

(I'll try to remember it.)

Bob Phillips wrote:

Nope, no good in 2000 or 97.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
xl2003, too. But I don't recall if that ever changed from previous

versions.

Bob Phillips wrote:

Still need to force a recalc, at least in XP you do.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

....


This tells excel to recalculate the function whenever excel

recalculates.

If I recall correctly, some versions of excel won't recalc when

columns
are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust

the
results.



Biff wrote:

Did you enter a valid range? (eg: =total_visible(A1:A4) )

Yes. I got it to return a sum but it doesn't change when columns are
hidden
and I calculate.

I had changed the function name but didn't realize it was called

later
in
the procedure. I changed it back.

Biff

"Ardus Petus" wrote in message
...
Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time

(with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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
'---------

"starguy"

a
écrit
dans le message de news:
...

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


--
starguy


-----------------------------------------------------------------------
-
starguy's Profile:

http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953








--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


  #36   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Summing non hidden values in a range

CaptainQuattro may be on to something!

This could be done with GET.CELL but if a working UDF is available I'd go
with it.

Biff

"Bob Phillips" wrote in message
...
Can you explain that, it doesn't work for me?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"CaptainQuattro"
<CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in
message
news:CaptainQuattro.277voy_1146635100.8876@excelfo rum-nospam.com...

Here's a workaround that will work in any version of Excel:


In row 1 Column D enter =CELL("width",A2)

Copy to cells E1 through K1

in Cell L11 enter

=SUMIF(D1:K1,"0",D11:K11)


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile:

http://www.excelforum.com/member.php...o&userid=32763
View this thread:
http://www.excelforum.com/showthread...hreadid=537953





  #37   Report Post  
Posted to microsoft.public.excel.misc
Dominic
 
Posts: n/a
Default Summing non hidden values in a range

Bob,

I got this to work for me by changing the formula in D1 to:

=CELL("width",D2)

For Excel2k (at least) you need to force a recalc to get the totals in
column L to refresh.

I imagine that when you hide column X the value in X1 turns to zero, though
its hard to see to confirm. ;-)



"Bob Phillips" wrote:

Can you explain that, it doesn't work for me?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"CaptainQuattro"
<CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in
message news:CaptainQuattro.277voy_1146635100.8876@excelfo rum-nospam.com...

Here's a workaround that will work in any version of Excel:


In row 1 Column D enter =CELL("width",A2)

Copy to cells E1 through K1

in Cell L11 enter

=SUMIF(D1:K1,"0",D11:K11)


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile:

http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=537953




  #38   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

Exactly, you need to force a recalc, which is the same as the UDF. So it is
no better, and requires a lot of other formulae scattered about. Your
assumption on the value is exactly what I assumed, and can easily be checked
by doing
?range("D1").Value
in the immediate window in the VBIDE, after forcing the recalc of course,
and indeed it does show 0.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dominic" wrote in message
...
Bob,

I got this to work for me by changing the formula in D1 to:

=CELL("width",D2)

For Excel2k (at least) you need to force a recalc to get the totals in
column L to refresh.

I imagine that when you hide column X the value in X1 turns to zero,

though
its hard to see to confirm. ;-)



"Bob Phillips" wrote:

Can you explain that, it doesn't work for me?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"CaptainQuattro"
<CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in
message

news:CaptainQuattro.277voy_1146635100.8876@excelfo rum-nospam.com...

Here's a workaround that will work in any version of Excel:


In row 1 Column D enter =CELL("width",A2)

Copy to cells E1 through K1

in Cell L11 enter

=SUMIF(D1:K1,"0",D11:K11)


--
CaptainQuattro


------------------------------------------------------------------------
CaptainQuattro's Profile:

http://www.excelforum.com/member.php...o&userid=32763
View this thread:

http://www.excelforum.com/showthread...hreadid=537953






  #39   Report Post  
Posted to microsoft.public.excel.misc
JB
 
Posts: n/a
Default Summing non hidden values in a range

http://cjoint.com/?fewLLijimL

With this instruction, worksheet is calculate:

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

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

Cordialy JB

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
summing values appearing in col B when col A has been filtered choc_penguin Excel Worksheet Functions 3 February 3rd 06 12:18 PM
Summing a range that changes Jeff Excel Discussion (Misc queries) 6 January 9th 06 11:57 PM
Sumproduct not working when summing values between two numbers FlamencoKid Excel Worksheet Functions 8 September 13th 05 06:01 PM
Summing a variable range of columns Richard Buttrey Excel Worksheet Functions 9 July 28th 05 06:52 PM
Match function...random search? Les Excel Worksheet Functions 10 July 28th 05 11:54 AM


All times are GMT +1. The time now is 06:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"