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**hide

## 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 (8 Handy Methods)**

## 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 (5 Suitable Ways)**

**Similar Readings**

**How to Sum Selected Cells in Excel (4 Easy Methods)****[Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)****How to Sum Only Positive Numbers in Excel (4 Simple Ways)****Shortcut for Sum in Excel (2 Quick Tricks)****How to Sum Multiple Rows in Excel (4 Quick Ways)**

## 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:** **Sum Cells in Excel: Continuous, Random, With Criteria, etc.**

## 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 (5 Examples)**

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