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

 

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

SUMIF Function: One Criteria & Sum Range The SUMIF function can also be used to apply the criteria to one range and sum the corresponding values in a different range. So if you have a list of Sales Reps in one list and their Sales in another list and want to Sum the sales of only one of the Sales Reps, then the SUMIF...
WEEKDAY function: Introduction   What does it do? Returns the day of the week corresponding to a date.  The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. Formula breakdown: =WEEKDAY(Serial_Number, ) What it means: =WEEKDAY(Date, ) The WEEKDAY f...
In-Cell Bar Charts with the REPT Function When you are creating an Excel Dashboard and are limited by space and do not want to insert a chart, you can easily create an in-cell bar chart using the RPT (repeat) function. The RPT function uses the vertical bar character | as the first argument: text and references the va...
Vlookup in an Excel Table What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLOOKUP(thi...

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 *