Excel: How do I sum fields but not the hidden ones?

Answer 1

There is no readymade Excel function for that, but I wrote a VBA code for you which you just need to paste into your workbook:

' //
' // Start Code
' //
Function Violet(szRange As Range)
For Each szCell In szRange.Cells
If szCell.Rows.Hidden = False And szCell.Columns.Hidden = False Then
Violet = Violet + szCell.Value
End If
Next szCell
End Function
' //
' // End Code
' //

The syntax is the same as for the SUM function:
=VIOLET(A1:A8) would summarize all the visible cells from A1 to A8.

Answer 2

RadPoe has a great solution.
this will work if you hide the cells manually

BUT
If you use AutoFilter, and did filter your list and want to sum the filtered cells only then you can use
=SUBTOTAL(9, A:A)
this will sum the full A column except those are hidden by Autofilter

Read my profile, I am the XLMan

Answer 3

Locking or hiding a cell hasn't an effect for the sum.

A1=10
A2=5
A3=1000 LOCKED OR HIDDEN
A4=20
A5=2
A6=50

Let us say A3 is hidden or locked.
To find the sum except A3 use the formula
=SUM(A1:A2)+SUM(A4:A6)

Answer 4

I may not know some things, but I don't think you can.
If you can put the hidden data in the adjacent col., you can sum either the first col. or sum both cols. as one.

EX. sum(a1:a10) or sum(a1:b10)

If there is another way. I'll be interested.