How to Sum Only Filtered Data or Visible Cell Values in Excel


This post will guide you how to sum only visible cell values in Excel. How do I sum only visible cell values in a selected range with a formula in Excel. How to sum only visible rows when filtering data in your worksheet with VBA code in Excel.

Sum Filtered Data Only with Formula

Assuming that you have a list of data in range A1:C6, which contain sales s data. And you have filtered out the data that is greater than 5. Then you need to sum all filtered data only or all visible values without hidden values. How to achieve it. You can use a formula based on the SUBTOTAL function to achieve the result. Like this:


Type this formula into a blank cell and press Enter key to apply this formula. And then all visible value should be summed out.

sum only filtered data1

Sum Filtered Data Only with VBA Code

You can also write a User Defined Function with VBA code to achieve the same result. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

sum only filtered data2

Function SumFilteredData(myRange As Range)
    For Each myCell In myRange
        If myCell.Rows.Hidden = False And myCell.Columns.Hidden = False Then
            Total = Total + myCell.Value
        End If
    SumFilteredData = Total
End Function

#5 back to the current worksheet, then type the following formula in a blank cell. press Enter key.


sum only filtered data3

Video: Sum Filtered Data Only (Sum Visible Cell values Only)


Related Functions

  • Excel SUBTOTAL function
    The Excel SUBTOTAL function returns the subtotal of the numbers in a list or database. The syntax of the SUBTOTAL function is as below:= SUBTOTAL (function_num, ref1, [ref2])….


Leave a Reply