-
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:
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:
Pingback: Exploring the Difference Between Total and Subtotal in Accounting – Tech guide
Such a beautiful article. So clearly, concisely and properly explained.
I have been able to absorb everything with one read.
Thank you so much! 🙂
LikeLike
You are welcome! Glad you like it. 😊
LikeLike
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.
LikeLike
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!
LikeLike
Thanks for your kind words, ClassicKnight. 🙂
LikeLike
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:
http://chandoo.org/wp/2015/08/25/summarize-filtered-values/
LikeLike
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.
Cheers,
LikeLike
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.
LikeLike
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.
Cheers,
LikeLike
Pingback: how to use sumif with filtering
Pingback: Counting Filtered data cells, copy and paste values from another workbook