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

triegue 

If you have a relatively new version of Excel, you can use the FILTER function instead of the Filter icon on the toolbar. You don't provide a lot of info, however.

The formula would be something like =SUM(FILTER(range, criteria))

If you want more specific help, you could help us help you by posting a copy of the spreadsheet (or a mockup, if the real contains confidential info) on OneDrive or GoogleDrive, with a link pasted here that grants access.

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