# Sum Visible Rows

When you’ve filtered data in Excel, the SUM function still sums cells even if they’re not visible. To SUM only the visible data, you can use the SUBTOTAL function. SUBTOTAL ignores hidden rows and columns.

In this example, there are 2156 rows of data that are filtered so that only those rows whose City is ‘Paris’ are shown.

The formulas below the filtered data are:
```C2159 - =SUM(C2:C2157) D2159 - =SUM(D2:D2157) C2160 - =SUBTOTAL(9,C2:C2157) D2160 - =SUBTOTAL(9,D2:D2157)```

You can do more than just sum with SUBTOTAL. The first argument (9) is what tells SUBTOTAL to sum. Look up SUBTOTAL_worksheet_function in help to see the other possible arguments.

## 14 thoughts on “Sum Visible Rows”

1. Peter says:

Interestingly, the subtotal function only works this way with “filtered” lists, and continues to sum rows which are just “hidden”.

2. Juan Pablo says:

Peter,

That is one of the new features in Excel 2003. They introduced new parameters to fix that issue.

3. Ana says:

Hi,

And how do we do this in a Macro?
How do I navigate between the visible rows only?

A.

4. excelerater says:

in excel 2202, i entered the following

a1 = subtotal(9,a:a)
a2 = data autofilter
a3 = data

a99 = data

it worked when i first created it, to automatically
add up the visible data that was filtered by the
autofilter.
now it stopped working and complains about a circular reference. the “subtotal” function is suppose to be smart enough to ignore other subtotals so values are not double counted.
i want the total at the top, so that it is always visible and so that i can add more data at the bottom

anyone have any ideas?

5. Thank you! Saved few hours of my time today.

6. Gaurav Suman says:

You’ve saved my fingers a lot of pain :)

7. capricorn116 says:

This will work. To sum it up with subtotal and not include hidden rows use a formula like this =SUBTOTAL(109,A2:A5) where 109 tells the subtotal function to sum and ignore hidden rows.

MS reference for the subtotal function http://office.microsoft.com/en-us/excel/HP100624631033.aspx.

8. nice tip. saved my time and effort to write vba codes.

9. D.K.Shrivastava says:

What is 9 (9,range)

10. Mahinth says:

Great tip. helped me a lot and saved lots of time

Excellent post.

For the other function values,

Column 1: Function_num (includes hidden values)
Column 2 : Function_num (ignores hidden values)
Column 3 : Function

1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

So, a “9? is a SUM() of all visible and hidden cells in the range and a “109? is a SUM() of only visible cells in the range.

It took me a while to realise that hidden is not the same as filtered. Filtered rows aren’t hidden according to MS. Semantics is obviously not a strong point in the MS camp.

But, I very very rarely manually (or programmatically) hide rows, but I filter all the time.

Thanks Dick,

12. But strange ….
I try on horizontal way…. =SUBTOTAL(9,H2:AI2)
The formula is still count Hidden cell.

I have no idea why?