How to Sum Only Visible Cells in Excel (4 Quick Ways)

We have a dataset of some employees. It contains four columns: Employee name, Department, working Hours per day, and Salary. Here’s an overview of the dataset we’ll use to sum up values in visible cells only.

Method 1 – Summing Only Visible Cells with a Table in Excel

Select all data from your datasheet.

Go to the Insert tab and select Table.

NOTE: You can also press Ctrl + T.

Go to the Design ribbon and select Total Row.

This will insert a row of the totals. We will see the sum there.

If we hide some rows, the value of the Total Row will automatically change and provide the sum of the visible cells only. We hid the 7th, 9th, and 10th row, and the sum for the visible cells appeared in the last row.

Read More: Sum to End of a Column in Excel

Method 2 – Using the AutoFilter to Sum Only Visible Cells in Excel

Case 2.1 – Use the SUBTOTAL Function

Select the entire range of cells in the dataset.

Go to the Data ribbon and select Filter.

Select cell E13 and copy the following formula.

=SUBTOTAL(109,E5:E12)

Press Enter to see the result.

If we filter any column, the result of the sum will change accordingly and display the sum of visible cells.

Case 2.2 – Use the AGGREGATE Function

Apply Filter to the entire dataset.
Select Cell E13.
Copy the following formula into the cell:

=AGGREGATE(9,5,E5:E12)

Press Enter.

If we filter any columns, it will only show the sum of the visible cells.

Case 2.3 – Use AutoSum

Apply a Filter to the dataset.
Select Cell E13.

Go to the Formulas ribbon and select AutoSum.

This will sum the Salary Column and show it in the cell.

Read More: How to Sum Filtered Cells in Excel

Method 3 – Finding a Sum for Visible Cells with a User-Defined Function

Go to the Developer ribbon and select Visual Basic.

The Microsoft Visual Basic window will appear. Insert a Module and copy the following code into it:

Function ONLYVISIBLE(CellRng As Range) As Double
Dim x As Range
Dim SumUp As Double
For Each x In CellRng
    If x.Rows.Hidden = False And x.Columns.Hidden = False Then
        SumUp = SumUp + x.Value
    End If
Next
ONLYVISIBLE = SumUp
End Function

Select Cell E13 and copy the following formula.

=ONLYVISIBLE(E5:E12)

Press Enter.

If we hide some rows, the sum will adjust accordingly.

Read More: How to Sum Random Cells in Excel

Method 4 – Applying the Excel SUMIF Function to Add Visible Cells

Add two new columns to the previous dataset: a Yes/No Column D and a Helper Column F.
Select Cell F5 and copy the following formula into it.

=AGGREGATE(9,5,E5)

Press Enter. This just sums up Cell E5 and displays the result. Use the Fill Handle to fill the column.

We will get all the values in the Helper Column.

Input the following formula in cell E13:

=SUMIF(D5:D12,”Yes”,F5:F12)

This will search for Yes in the range D5 to D12 and sum the respective salary if the check yields TRUE.
If we use a Filter, it will only display the sum of the rows if its respective status is Yes.

Download Practice Workbook

Download this workbook and practice.

Sum Only Visible Cell.xlsm