A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Summing non hidden values in a range



 
 
Thread Tools Display Modes
  #11  
Old May 2nd, 2006, 11:41 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 11:54 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 12:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 12:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 01:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 01:37 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 06:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 07:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 07:48 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 3rd, 2006, 03:11 AM posted to microsoft.public.excel.misc
external usenet poster
 
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula: If 2 values (in a range of six) are >3 then TRUE, FALSE IFfunction General Discussion 2 October 10th, 2005 10:34 AM
Summing values within a range rmellison General Discussion 7 September 2nd, 2005 12:43 PM
Cell linked to a range of cell values in different sheet szeng General Discussion 1 August 9th, 2005 02:41 AM
Want to put range values in a column into two columns Arch Worksheet Functions 7 June 15th, 2004 11:49 PM
Dynamic Offsetting, Names, Arrays, Range, Chart Values. NEED HELP Bob Lidgard Charts and Charting 14 February 25th, 2004 04:26 PM


All times are GMT +1. The time now is 07:28 AM.


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