SUBTOTAL formula in Excel with Examples

Do you often work with long lists of data and need a quick way to summarize information? If yes, the SUBTOTAL formula in Excel will quickly become your best friend! In this post, I’ll Walk you through how to use the SUBTOTAL formula effectively with examples. By the end, you’ll feel confident using it to summarize data without breaking a sweat. Let’s dive in!

Table of Contents

What is the SUBTOTAL Formula in Excel?

The SUBTOTAL is a versatile tool designed to perform various functions, such as SUM, AVERAGE, COUNT, and more, on a filtered or selected range of data. Unlike the regular SUM function, SUBTOTAL automatically ignores hidden rows, which makes it ideal for dynamic reports and filtered data sets.

How Does the SUBTOTAL Formula Work?

The syntax of the SUBTOTAL formula is:

=SUBTOTAL (function Num, range)

function Num: This number defines which function to apply (e.g., SUM, AVERAGE).
range: The range of cells to apply the function.

For example:

9 represents the SUM function.
1 represents the AVERAGE function.

In our example, we’ll use function number 9 (SUM) to get the totals for the quantity and amount columns.

Example Data: Product Sales

Below is the dataset we’ll use to demonstrate the SUBTOTAL formula. It includes product sales information with four key columns:

Product
Quantity (Qty)
Rate
Amount

Applying the SUBTOTAL Formula

Now that we have our data, let’s see how the SUBTOTAL formula works for summarizing the Quantity (Qty) and Amount columns.

To calculate the total quantity and total amount, use the following formulas:

Formula for Total Quantity:

=SUBTOTAL (9, B4:B21)

Formula for Total Amount:

=SUBTOTAL (9, D4:D21)

Why Use SUBTOTAL Instead of SUM?

When dealing with large data sets, you may want to filter out some rows to focus on specific categories. This is where the SUBTOTAL formula shines. It only considers visible rows after filtering, making it perfect for real-time reports.

For example, if you filter the data to only show products with quantities greater than 20, the SUBTOTAL formula will update automatically, displaying only the totals for the filtered items.

Key Benefits of Using the SUBTOTAL Formula

Here are three reasons why the SUBTOTAL formula should be in every Excel user’s toolkit:

Dynamic Summaries: Automatically adjusts totals when rows are filtered or hidden.
Multiple Functions in One: Perform SUM, AVERAGE, COUNT, and more with a single formula.
Perfect for Reporting: Essential for creating dashboards and reports where data is constantly changing.

Final Thoughts

The SUBTOTAL formula in Excel is an excellent tool for anyone working with lists, tables, or reports. Whether you need a dynamic summary or want to generate quick insights from filtered data, SUBTOTAL is the way to go.

Try it out yourself! Use the formulas we’ve shared and see how easy it is to manage totals efficiently. If you want a deeper dive into Excel formulas, check out the YouTube video below, where we demonstrate this formula in action:

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert