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
  #21  
Old May 3rd, 2006, 03:26 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 3rd, 2006, 04:54 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 3rd, 2006, 05:21 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 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  
Old May 3rd, 2006, 05:57 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 3rd, 2006, 06:06 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 3rd, 2006, 06:24 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 3rd, 2006, 06:40 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 3rd, 2006, 08:59 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 3rd, 2006, 01:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
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
 




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 08:25 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.