The SUBTOTAL function in Excel offers numerous beneficial features, such as the ability to: Calculate SUM, AVERAGE, COUNT, COUNTA, MAX, or MIN from your data. Include hidden values in your calculations by using the first argument function_num with values between 1-11. Exclude hidden values from your calculations by using the first argument function_num with values between 101-111. Calculate the SUBTOTAL of filtered values. Additionally, it can ignore other SUBTOTALS within your range to prevent double counting.

Key Takeaways

• Calculate Only Visible Data – The SUBTOTAL function computes values for visible rows, ensuring no double-counting in filtered lists.
• Supports Various Operations – SUBTOTAL can execute functions like SUM, AVERAGE, COUNT, and others, using different function numbers.
• Adapts to Filtering – Unlike SUM, SUBTOTAL dynamically updates when filters are applied, making it perfect for dynamic reports.
• Excludes Hidden Rows – By using function numbers 101–111, you can ignore both manually hidden and filtered rows.
• Ideal for Nested Subtotals – In Pivot Tables or structured reports, SUBTOTAL avoids repeating cumulative totals in groupings.

Table of Contents

Quick Overview

What does it do?

It calculates 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 represents

PREVENTING DOUBLE COUNTING WITH THE SUBTOTAL FUNCTION…

This feature is likely the most valuable aspect of the SUBTOTAL function!

Imagine you have multiple SUBTOTALS within your dataset, one to Sum the North Region and another to Sum the South Region.

You can then add a third SUBTOTAL for your Grand Total that encompasses all your data while ignoring the North & South Region SUBTOTALS, thus ensuring no double counting in your Grand Total.

See the below images to understand how this works with the SUBTOTAL function and how it leads to double counting when using the SUM function:

*Values for the SUBTOTAL function_num:

Frequently Asked Questions

What is the purpose of the SUBTOTAL function in Excel?
It calculates functions such as SUM, AVERAGE, COUNT, etc., but only on visible (non-filtered) data, helping you avoid including hidden rows in your calculations.

How does SUBTOTAL differ from SUM?
SUM calculates all values, whereas SUBTOTAL can exclude hidden or filtered-out rows based on the function number used.

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

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

Why is SUBTOTAL more suitable for dynamic reports?
It automatically adjusts when you apply filters, providing accurate summaries without the need for manual adjustments.

The above is the detailed content of Excel Subtotal Function – Avoid Double Counting. For more information, please follow other related articles on the PHP Chinese website!