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.

**Table of Contents**Expand

## 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 Function
```

Here, 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 t**o 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.