I may be wrong but I don't think that you can just sum visible cells with a native excel formula.
However, if you need to do it, one alternative is to use an UDF and use it like a formula, like
I did an example, just paste the code in a regular module.
Hope this helps
Function SumVisible(rR As Range) As Double
Dim rC As Range, dtotal As Double
For Each rC In rR
If Not rC.EntireRow.Hidden And Not rC.EntireColumn.Hidden Then dtotal = dtotal + rC
SumVisible = dtotal