Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 2nd 06, 06:33 AM 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  
Old May 2nd 06, 06:57 AM 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  
Old May 2nd 06, 07:06 AM 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  
Old May 2nd 06, 07:17 AM 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  
Old May 2nd 06, 07:20 AM 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  
Old May 2nd 06, 07:42 AM 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  
Old May 2nd 06, 08:15 AM 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  
Old May 2nd 06, 08:44 AM 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  
Old May 2nd 06, 09:14 AM 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  
Old May 2nd 06, 11:18 AM 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



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 11:18 AM
Summing a range that changes Jeff Excel Discussion (Misc queries) 6 January 9th 06 10: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 09:32 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017