Summing a column excluding hidden cells

[Frank S.]

I have a column of numbers. I wish to sum only those numbers whose cells
are not hidden. How do I do this?
Thanks in advance.

Frank S.

 

[Harald Staff]

Hi Frank

If they're hidden useing Autofilter then use the SUBTOTAL function for this.
If it's hidden by rightclicking, menus, woodoo and/or bogus row heights then
I believe it's impossible.

HTH. Best wishes Harald

 

[Bob Umlas]

If you have Excel 2003, then you can use the SUBTOTAL Function with the NEW
function numbers to reference HIDDEN cells (in addition to FILTERED rows) --
add 100. So instead of =SUBTOTAL(9,A1:A100), you can use
=SUBTOTAL(109,A1:A100)

Bob Umlas
Excel MVP

 

[Peo Sjoblom]

Unless you have Excel 2003 where you can use subtotal on non filtered hidden
rows/columns

 

[RagDyeR]

If you just need to know the value of the addition, set the status bar to
display Sum, select the cells, and read the displayed value.

You might also try this:
Insert a "helper" column, and *copy* the visible cells, and then sum the
copy.

Select the cells in question, then hit <F5>,
Click "Special",
Click "VisibleCellsOnly",
<OK>

Now, right click in the selection, and choose "Copy",
Click in the top cell of the helper column and hit <Enter>.
You could then drag your original SUM formula into this column, and you
should have your "Visible Total".

If you wish to do this often, there is a "SelectVisibleCells" icon that you
can place on your toolbar.
Right click on the toolbar, and choose "Customize".
Under the "Commands" tab, click "Edit" in the left window,
And then scroll down almost to the bottom of the right window to the
"SelectVisibleCells" icon.
Click and drag it to your toolbar.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I have a column of numbers. I wish to sum only those numbers whose cells
are not hidden. How do I do this?
Thanks in advance.

Frank S.

 

[Frank S.]

Thank you.
It works exactly as I needed!
Frank S.

 

[CLR]

Another alternative would be to create a Non-Contiguous Range to include
only the cells you wish to sum and then just

=SUM(YourNonContiguousRangeName)

Vaya con Dios,
Chuck, CABGx3