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

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.


Download Practice Workbook

Download this workbook and practice.


4 Ways 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, their working Hour per day, and their Salary respectively. Here’s an overview of the dataset for the following methods.

sum only visible cells

1. Sum Only Visible Cells with 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.

Sum Only Visible Cells with Table in Excel

  • Secondly, go to the INSERT ribbon and select Table.

Sum Only Visible Cells with Table in Excel

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.

Sum Only Visible Cells with Table in Excel

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

Sum Only Visible Cells with Table in Excel

  • Now, if we hide some columns the value of the Total Row will automatically change and give us the sum of the visible cells only.

Sum Only Visible Cells with Table in Excel

Here, we hid the 7th, 9th & 10th rows and the sum for the visible cells appeared in the last row.

Read more: Sum Cells in Excel


2. 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.

AutoFilter to Sum Only Visible Cells in Excel

  • Then go to the DATA ribbon and select FILTER.

AutoFilter to Sum Only Visible Cells in Excel.

  • Now, select Cell E13 and type the formula.
=SUBTOTAL(109,E5:E12)

AutoFilter to Sum Only Visible Cells in Excel.

  • Now, select Enter to see the result.

AutoFilter to Sum Only Visible Cells in Excel.

  • Lastly, if we filter any column, the result of the sum will change accordingly and it will only display the sum of visible cells.

AutoFilter to Sum Only Visible Cells in Excel.


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)

 AutoFilter to Sum Only Visible Cells in Excel.

  • Now, select Enter and see the result.

AutoFilter to Sum Only Visible Cells in Excel.

  • Finally, if we filter any columns, it will only show the sum of the visible cells.

AutoFilter to Sum Only Visible Cells in Excel.


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.

AutoFilter to Sum Only Visible Cells in Excel.

  • Secondly, go to the FORMULAS ribbon and select AutoSum.

AutoFilter to Sum Only Visible Cells in Excel.

  • Finally, it will sum the Salary Column and show it in the cell.

Read more: How to Add Multiple Cells in Excel


Similar Readings


3. Find 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 Function

Here, our function is ONLYVISIBLE.

  • Thirdly, select Cell E13 and type the formula.
=ONLYVISIBLE(E5:E12)

Find Sum Only for Visible cells with User-Defined Function

  • Now, press Enter and see the result.

Find Sum Only for Visible cells with User-Defined Function

  • Finally, if we hide the 7th, 9th &10th row, it will show the desired result.

Read more: Excel Sum Last 5 Values in Row


4. 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: How to Add Specific Cells in Excel


Conclusion

In the end, I want to say, 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

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo