• How to SUM a range but ignores SUM result in between?, i.e. How to get a Grand Total with SubTotals in between?

  • How to SUM but ignores hidden cells or filtered cells?

SUM could be the most popular function used in spreadsheet.  However there are situations that SUM may not be the best simple solution. For example, getting Grand Total in a column where many subtotals in between:

Excel Tips - SUM vs SUBTOTAL1

A simple SUM for the whole range may double count as it includes all the subtotals in between.

For the example above, using SUBTOTAL instead of SUM offers you a simple solution as SUBTOTAL is designed for ignoring  SUBTOTAL in between.

You may think of an even simpler way: =SUM(Range)/2 to tackle the double count issue. That could be true, just to make sure you won’t need to remove all subtotals one day in the future.  Also what if you need to calculate the AVERAGE instead of SUM? Do you have a simple formula like =AVERAGE(Range)/2?  I don’t think so.

The beauty of SUBTOTAL is the variety of functions it offers.  In Excel 2010, there are 11 functions for SUBTOTAL, as shown below:

ImageFrom Excel Help

The syntax

SUBTOTAL(function_num,ref1,[ref2],…]) ‘where

  • function_num is from 1 to 11 or 101 to 111 that specifies which function to be deployed
  • Ref1 is the range of data you want the function to apply to
  • Ref2,3… are optional, just in case you have non-contagious ranges

For me, I use functions 3 (COUNTA) and 9 (SUM) most often, 1 (AVERAGE) sometimes.

How to SUM but ignores hidden cells or filtered cells?

Let’s compare the difference between SUM(Range), SUBTOTAL(9,Range) and SUBTOTAL(109,Range)

When there is no hidden/filtered rows, they all give you the same result.


However, if some rows are hidden (by Format –> Hide/Unhide or by Grouping).  They behave differently (see below):


Both SUM(Range) and SUBTOTAL(9,Range) give the original result; while SUBTOTAL(109,Range) gives you the summation of  visible cells only.

When AutoFilter is turned ON and applied, SUBTOTAL(9,Range) and SUBTOTAL(109,Range) basically behave in the same way: i.e. exclude hidden values.


Even if you HIDE some rows after the filtered is ON, both SUBTOTALs give you exactly the same result:



Before I know how to use Pivot Table and SUMIF(S), I used a combination of AutoFilter and SUBTOTAL to solve sum if questions. For example, if you want to find out the total sales of Product A to China (two criteria here; supposed to use SUMIFS),  I simply use Subtotal(9,Range) and then applied filters accordingly.  Not that efficient actually (when I look back).



  • SUM always gives you a static result regardless visibility; not good in getting Grand Total if Subtotal is in between
  • SUBTOTAL is smart in getting Grand Total as it ignores SUBTOTAL in between
  • SUBTOTAL(9,Range) gives a result including hidden cells
  • SUBTOTAL(109,Range) gives a result excluding hidden cells
  • Both SUBTOTALs behave the same when AutoFilter is ON and applied; excluding hidden cells (regardless of way of hiding them)

Other examples of SUBTOTAL:

This entry was posted in Excel Tips, Formula and tagged , . Bookmark the permalink.

8 Responses to SUM vs. SUBTOTAL

  1. The clearest and simplest explanation I’ve been able to find about the subtotal function. Subtotal the Grand Total……who would have thought? Works like a charm!


  2. David Henderson says:

    Wow! Excellent blog post. I now understand the nuances of the subtotal function like never before. Didn’t realise that it excluded other subtotal functions from above and also that the functions no’s 1,2,3 are alphabetical AVERAGE, COUNT …

    Have you though about following up this post with the aggregate function so that error values can be excluded like hidden rows etc.

    Think chandoo has made a start to this topic:


    • MF says:

      Hi David,
      Thanks for your kind words. Glad that it helps you to understand the SUBTOTAL better.
      Good idea to have a post for AGGREGATE. Actually it is the new function I love in Excel 2010. I will put it on my list of idea and will write a post of it in the future.


  3. Marcia says:

    Hi everyone, it’s my first go to see at this website,
    and piece of writing is truly fruitful designed for me,
    keep up posting such posts.


    • MF says:

      Hi Marcia,
      Thanks for your feedback and glad that you like it.
      I plan to post on weekly basis (except if I am traveling). Pls come back and visit. Comments are always welcome.


  4. Pingback: how to use sumif with filtering

  5. Pingback: Counting Filtered data cells, copy and paste values from another workbook

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s