Excel Subtotal Function – Avoid Double Counting

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!

Key Takeaways

• Summarize Filtered Data Only – The SUBTOTAL function calculates values for visible rows, helping avoid double-counting in filtered lists.

• Multiple Operations Supported – SUBTOTAL can perform functions like SUM, AVERAGE, COUNT, and more using different function numbers.

• Dynamic with Filters – Unlike SUM, SUBTOTAL automatically updates when you apply filters, making it ideal for dynamic reports.

• Avoid Counting Hidden Rows – Use function numbers 101–111 to ignore hidden (manually hidden) rows in addition to filtered rows.

• Perfect for Nested Subtotals – In Pivot Tables or structured reports, SUBTOTAL prevents cumulative totals from repeating in groupings.

Quick Overview

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

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:

Frequently Asked Questions

What does the SUBTOTAL function do in Excel?
It performs calculations like SUM, AVERAGE, COUNT, etc., but only on visible (non-filtered) data, helping you avoid counting hidden rows.

How is SUBTOTAL different from SUM?
SUM includes all values, while SUBTOTAL excludes hidden or filtered-out rows depending on the function number used.

Which function number should I use to exclude both filtered and hidden rows?
Use function numbers from 101 to 111 (e.g., 109 for SUM) to exclude manually hidden and filtered rows.

Can SUBTOTAL be used in Pivot Tables?
Pivot Tables have their own subtotal settings, but SUBTOTAL is useful in regular tables or data lists that use filters.

Why is SUBTOTAL better for dynamic reports?
Because it updates automatically when you filter data, giving accurate summaries without requiring manual adjustments.

John Michaloudis

Founder & Chief Inspirational Officer

at

MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.