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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 03:26 PM |