How To Sum Only Visible Cells In Excel (4 Quick Ways) - ExcelDemy

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.

Read More: Excel Sum If a Cell Contains Criteria

Download Practice Workbook

Download this workbook and practice.

Sum Only Visible Cell.xlsm

Further Readings

• How to Sum Columns in Excel
• Sum Multiple Rows and Columns in Excel
• How to Sum Colored Cells in Excel
• Calculate Cumulative Sum in Excel
• How to Add Multiple Cells in Excel

Get FREE Advanced Excel Exercises with Solutions!