Excel Subtotal Function – Avoid Double Counting

What does it do?

It returns a Subtotal in a list or database

Formula breakdown:

=SUBTOTAL(function_num, ref1)

What it means:

=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)

***Go to the bottom of this post to see what each value stands for


The SUBTOTAL function in Excel has many great features, like the ability to:

* Return a SUM, AVERAGE, COUNT, COUNTA, MAX or MIN from your data;

* Include hidden values within your data by entering the first argument function_num, as values between 1-11;

* Ignore hidden values within your data by entering the first argument function_num, as values between 101-111;

* Find the SUBTOTAL of filtered values;

* Ignore other SUBTOTALS that are included in your range, avoiding any double counting!

AVOIDING DOUBLE COUNTING WITH THE SUBTOTAL FUNCTION…

This is probably the most useful feature within the SUBTOTAL function!

Let’s say you have various SUBTOTALS within your data, one SUBTOTAL to Sum the North Region and another SUBTOTAL to Sum the South Region.

You can include a third SUBTOTAL for your Grand Total which references all of your data and ignoring the North & South Region SUBTOTALS, meaning that there is no double counting in your Grand Total.

See the below images of how this works with the SUBTOTAL function and how it double counts when using the SUM function:

sub1sub2

DOWNLOAD WORKBOOK

Subtotal Sum

***Values for the SUBTOTAL function_num:

Includes hidden values    Ignores hidden values    Function      
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

 

HELPFUL RESOURCE:

mrexcelXL

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Excel Subtotal Function – Filtered or Visible Valu... What does it do?It returns a Subtotal in a list or databaseFormula breakdown:=SUBTOTAL(function_num, ref1)What it means:=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)***Go to the bottom...
Free Excel Formulas & Functions Webinar Train... In this popular Free Excel Formulas & Functions Webinar Training which goes for 60 minutes you will learn the MUST KNOW Excel Formulas to ADVANCE your Excel skills!You will learn the following Excel Formulas & Functions: IF, SUMIF, VLOOKUP, INDEX/MATCH + MORE...!P...
Index Match 2 Criteria with Data Validation We can use the INDEX-MATCH formula and combine it with Data Validation drop down menus to return a value based on 2 criteria.This is a little advanced so you will need to drop what you are doing and really focus.  Let's go...First we need to convert our data into an Excel...
Excel´s TEXT Function The TEXT function in Excel allows you to convert a numeric value to a specific format by using special format strings.If you have a date and want to show just the month or if you have a large number and want to show it in a thousands format , then the TEXT function is your sa...

DO YOU WANT TO GET BETTER AT EXCEL?
If so, join over 80,000 professionals who get career boosting, Free Excel lessons delivered on a weekly basis & receive our Free Excel Keyboard Shortcuts Template

Click here to subscribe

Leave a Comment

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