There are a few ways to Sum Only Visible Cells in Excel. Today we will go through 4 quick ways to Sum Only Visible Cells. Frequently, we need to hide or filter data in our workbook for productive analysis in Excel. The default SUM Function will not work in this case because it sums up all the values in a range of cells. We can use the following methods to Sum Only Visible Cells in Excel.
Let us assume, we have a dataset of some employees of a company. The dataset contains four columns; Employee name, Department, working Hour per day, and their Salary respectively. Here’s an overview of the dataset for the following methods.
1. Doing Sum Only for Visible Cells with a Table in Excel
In this method, we will calculate the sum only for visible cells in Excel. Here, we will convert our dataset into a table and then find the sum very easily. Follow the below steps to find the solution:
STEPS:
- Firstly, select the data from your datasheet.
- Secondly, go to the INSERT ribbon and select Table.
NOTE: This will convert the dataset into a table. You can also do it with the help of the keyboard. Just press Ctrl + T and it will convert the dataset into a table.
- Thirdly, go to the DESIGN ribbon and select Total Row.
- This will insert a row of the total. We will see the sum there.
- Now, if we hide some rows the value of the Total Row will automatically change and give us the sum of the visible cells only.
Here, we hid the 7th, 9th & 10th rows and the sum for the visible cells appeared in the last row.
Read More: Sum to End of a Column in Excel
2. Performing AutoFilter to Sum Only Visible Cells in Excel
We use the Filter feature of Excel to sum only visible cells. Here, we can use the SUBTOTAL Function and AGGREGATE Function in this method. We will also show the use of AutoSum here.
We are going to use the previous dataset again.
2.1. Use of SUBTOTAL Function
The SUBTOTAL Function returns a subtotal in a dataset. We will use this function to sum only visible cells here. We need to apply the filter to our dataset to perform this method.
STEPS:
- Firstly, select the range of cells in the dataset.
- Then go to the DATA ribbon and select FILTER.
- Now, select Cell E13 and type the formula.
=SUBTOTAL(109,E5:E12)
- Now, select Enter to see the result.
- Lastly, if we filter any column, the result of the sum will change accordingly and it will only display the sum of visible cells.
2.2. Use of AGGREGATE Function
It is pretty much similar to the SUBTOTAL Function method. We use the AGGREGATE Function instead of SUBTOTAL.
STEPS:
- Firstly, select Cell E13.
- Now type the formula:
=AGGREGATE(9,5,E5:E12)
- Now, select Enter and see the result.
- Finally, if we filter any columns, it will only show the sum of the visible cells.
2.3. Use of AutoSum
In the process, we need to filter the dataset at first and then use the AutoSum feature. Otherwise, it will not give the result we want.
STEPS:
- Firstly, select Cell E13.
- Secondly, go to the FORMULAS ribbon and select AutoSum.
- Finally, it will sum the Salary Column and show it in the cell.
Read More: How to Sum Filtered Cells in Excel
3. Finding Sum Only for Visible Cells with User-Defined Function
We can use the VBA feature of Microsoft Excel to create our own function that will sum up the visible cells in a datasheet.
We will use the following steps here.
STEPS:
- Firstly, go to the DEVELOPER ribbon and select Visual Basics.
- Secondly, the Microsoft Visual Basic window will appear. Select the method and type the code there.
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 FunctionHere, our function is ONLYVISIBLE.
- Thirdly, select Cell E13 and type the formula.
=ONLYVISIBLE(E5:E12)
- Now, press Enter and see the result.
- Finally, if we hide the 7th, 9th &10th row, it will show the desired result.
Read More: How to Sum Random Cells in Excel
4. Applying Excel SUMIF Function to Add Visible Cells
Sometimes, we need to use some criteria to find our expected results. In those cases, we can use the SUMIF Function.
Here, we will add two new columns in the previous dataset to perform this operation. We will use a Yes/No Column and a Helper Column for this method. We will take the help of the AGGREGATE Function here.
STEPS:
- Firstly, select Cell F5 and type the formula.
=AGGREGATE(9,5,E5)
- Secondly, press Enter and see the result. It just sums up Cell E5 and displays the result. Use the Fill Handle to fill the next cells.
- Thirdly, we will see the values in the Helper Column.
- Then put the formula in Cell E13.
=SUMIF(D5:D12,”Yes”,F5:F12)
It will search for criteria in the range D5 to D12 and sum if the condition satisfies.
- Finally, if we use Filter, it will only display the sum of the cells where the criteria satisfy.
NOTE: We can only use this method if we need to apply certain conditions.
Read More: Excel Sum If a Cell Contains Criteria
Download Practice Workbook
Download this workbook and practice.
Conclusion
In the end, I want to say, that there are sometimes when we face these types of problems in Excel. We have seen four easy methods here. I hope these methods will help you find the solution to your problem. Last of all, if you have any queries or suggestions, feel free to comment below.
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
