Summing filtered cells and preserving the answer when filter is turned off

triegue 

I don't know of a built-in way to do this.

Here is a macro you can use for this purpose, with some limitations:

• It will only work if you create the formula on the same worksheet as the range to be summed.
• It will probably break down if the number of disjoint ranges to be summed is large.

The macro will prompt you to select the range to be summed (by default the current selection), then to select the cell where the formula should be created.

Sub SumFilteredCells()
    Dim SumRange As Range
    Dim FormulaCell As Range
    On Error Resume Next
    Set SumRange = Application.InputBox( _
        Prompt:="Select the range to sum", _
        Default:=Selection.Address, _
        Type:=8)
    If SumRange Is Nothing Then Exit Sub
    Set FormulaCell = Application.InputBox( _
        Prompt:="Select the cell to place the formula in", _
        Type:=8)
    If FormulaCell Is Nothing Then Exit Sub
    Set SumRange = SumRange.SpecialCells(xlCellTypeVisible)
    Set FormulaCell = FormulaCell(1)
    FormulaCell.Formula = "=SUM(" & SumRange.Address & ")"
End Sub