software

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: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=537953Hi!

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

quot;starguyquot; gt; wrote in
message ...
gt;
gt; I have data in a range of D11:K11 and its sum in cell L11. This goes
gt; down to around 30 rows. Sometimes I have to hide columns in range of
gt; D11:K11 and want to sum the non hidden cells in L11 and down to 30
gt; rows.
gt; Is there any formula or function???
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=537953
gt;

starguy Wrote:
gt; I have data in a range of D11:K11 and its sum in cell L11. This goes
gt; down to around 30 rows. Sometimes I have to hide columns in range of
gt; D11:K11 and want to sum the non hidden cells in L11 and down to 30
gt; rows.
gt; Is there any formula or function???Try =SUBTOTAL(9,D11:K11)--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: www.excelforum.com/member.php...oamp;userid=32763
View this thread: www.excelforum.com/showthread...hreadid=537953With UDF (Excel lt;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 FunctionPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
calcultate
End Sub

Cordialy JB
using Excel 2003 but it did not work.--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=537953
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: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=537953How have you hidden the rows?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;starguyquot; gt; wrote in
message ...
gt;
gt; thanks for reply but I dont know to implement UDF because I dont know
gt; VB.
gt; I m using Excel 2003 then why this function does not work???
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=537953
gt;

I have hidden columns not rows. I hide them by both ways using Ctrl 9
and by menu Format gt; Column gt; Hide but nothing happened after hiding. I
also pressed F9 to recalculate after hiding columns.Bob Phillips Wrote:
gt; How have you hidden the rows?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=537953Something like =SUBTOTAL(109,A1:E1) should work in those circumstances.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;starguyquot; gt; wrote in
message ...
gt;
gt; I have hidden columns not rows. I hide them by both ways using Ctrl 9
gt; and by menu Format gt; Column gt; Hide but nothing happened after hiding. I
gt; also pressed F9 to recalculate after hiding columns.
gt;
gt;
gt; Bob Phillips Wrote:
gt; gt; How have you hidden the rows?
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=537953
gt;

its not working. i m sending you sample workbook. please check what is
the problem. -------------------------------------------------------------------
|Filename: Book1.zip |
|Download: www.excelforum.com/attachment.php?postid=4714 |
-------------------------------------------------------------------

--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=537953