# Excel Subtotal Function – Avoid Double Counting

Author: - Posted on November 3, 2015

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:

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

If you like this Excel tip, please share it

email

Pinterest

• Bunny says:

Perfectly explanined 1-11 & 101 -111 of Subtotal.
you nailed it John.

• Bryan says:

Hi Bunny,