• 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:

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:

From 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:

Application:

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

Conclusions:

• 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:

• Sequential number for visible rows only
• Put a dynamic hint for showing or hiding rows or columns hidden by Data Group