## Sum only visible cells

The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture above shows a cell range that has row 4 and 9 hidden. The usual SUM function won't work in this case, you need the SUBTOTAL function:

The first argument allows you to pick a function number that determines how the SUBTOTAL function behaves. In this case, 109 sums all visible cells in a cell range.

To hide a value simply right click on a row number and then click on "Hide" to hide the entire row. Select the rows around a hidden row and then right click on them to open a menu, there click on "Unhide" to show the value again.

The picture above shows filtered values in column C. Excel tells you that the cell range is filtered by the color of the row numbers and the icon next to "Numbers" in cell C2.

To apply a filter to a column simply select the cell range, go to tab "Data" on the ribbon, click on "Filter" button. A black down-pointing arrow appears next to header name "Numbers", click on it to apply a filter.

The Excel defined table above has a built-in feature that allows you to sum filtered values automatically, all you need to do is select a cell in the table, go to tab "Design" on the ribbon, then click on check-box "Total Row" to show the totals.

Cell C13 in the picture above displays the total for filtered cells. The SUBTOTAL function works just as well if you prefer using an Excel function with an Excel defined table, demonstrated in cell C15.

### Download excel *.xlsx file

VLOOKUP in a filtered Excel Table and return multiple values

This post describes how to search filtered values in an Excel defined Table using a condition given in cell 12 and return […]

Learn how to use the SUBTOTAL function

The picture above shows row 4 and row 8 hidden, the SUM argument 9 sums all values in C3:C11 whereas […]

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

Running totals based on criteria

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell? Can I have […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form