| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| summing values appearing in col B when col A has been filtered | Excel Worksheet Functions | |||
| Summing a range that changes | Excel Discussion (Misc queries) | |||
| Sumproduct not working when summing values between two numbers | Excel Worksheet Functions | |||
| Summing a variable range of columns | Excel Worksheet Functions | |||
| Match function...random search? | Excel Worksheet Functions | |||
Hybrid Mode
