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
  #1  
Old May 2nd, 2006, 06:33 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 06:57 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 07:06 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 07:17 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 07:20 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 07:42 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 08:15 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 08:44 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 09:14 AM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old May 2nd, 2006, 11:18 AM posted to microsoft.public.excel.misc
external usenet poster
 
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

 




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:02 PM.


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