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.

subtotal

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. Interestingly, the subtotal function only works this way with “filtered” lists, and continues to sum rows which are just “hidden”.

  2. Peter,

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

  3. 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?

  4. 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,

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

    I have no idea why?
    Please help….

    Thankss

Leave a Reply

Your email address will not be published. Required fields are marked *