Misc

Google Sheets Subtotal With Condition

Admin
Google Sheets Subtotal With Condition

How to Use Google Sheets SUBTOTAL with ConditionGoogle Sheets is a versatile tool for managing and analyzing data. One of its useful functions is SUBTOTAL, which lets you calculate values like sums or averages while ignoring hidden or filtered rows. But what if you need a subtotal that also meets a specific condition like summing only filtered rows that belong to a certain category? That’s where combining SUBTOTAL with conditional logic comes in handy.

In this topic, you’ll learn how to use SUBTOTAL with conditions in Google Sheets. We’ll explore practical methods, easy-to-follow examples, and tips to make your spreadsheets smarter.

Understanding the Basics of SUBTOTAL

Before diving into conditions, it’s important to understand how the SUBTOTAL function works.

The basic syntax is

=SUBTOTAL(function_code, range)

For example

=SUBTOTAL(9, B2B10)

This calculates the sum (function code 9) of values in the range B2 to B10, ignoring any rows that have been filtered out.

Function codes for SUBTOTAL include

  • 1 AVERAGE

  • 2 COUNT

  • 3 COUNTA

  • 9 SUM

  • 101 AVERAGE (ignores hidden rows)

  • 102 COUNT (ignores hidden rows)

  • 109 SUM (ignores hidden rows)

These codes let you control whether to include hidden rows or only visible ones.

Can You Add a Condition to SUBTOTAL?

The short answer is not directly. The SUBTOTAL function does not accept conditions like IF or FILTER inside it the same way that SUMIFS does. However, you can combine it with other formulas to achieve a similar result.

Let’s explore how.

Method 1 Using FILTER Inside SUBTOTAL

You can wrap the FILTER function around a data range to include only the rows that meet your condition. Then use SUBTOTAL to calculate the result.

Example

Suppose column A contains product categories and column B contains sales numbers. You only want to subtotal the sales of “Electronics that are currently visible after filtering.

Here’s the formula

=SUBTOTAL(9, FILTER(B2B100, A2A100 = "Electronics"))

This returns the sum of values in column B where the corresponding value in column A is "Electronics". It respects filters, so if some rows are hidden by filter, they won’t be included in the result.

Method 2 Subtotal with Dynamic Category Selection

If you want to make your formula more flexible, you can refer to a cell for the condition.

Example

  • A2A contains categories

  • B2B contains sales

  • D1 contains the selected category (e.g., “Furniture)

Then use

=SUBTOTAL(9, FILTER(B2B100, A2A100 = D1))

This allows users to change the value in D1 and see the subtotal update automatically. It’s a great way to make interactive dashboards.

Method 3 Combining SUBTOTAL with ARRAYFORMULA and IF

Sometimes, you might want to apply multiple conditions or add more complexity. Using ARRAYFORMULA with IF allows for greater control.

Example

You want to sum values in B2B100 only if column A has “Books and the rows are not hidden.

Google Sheets doesn’t let SUBTOTAL handle multiple conditions directly. Instead, use a helper column.

  1. In column C, enter
=ARRAYFORMULA(IF(A2A100="Books", B2B100, ""))

  1. Then apply filter to your sheet.

  2. Use

=SUBTOTAL(9, C2C100)

Only visible “Books values will be summed.

Method 4 Conditional SUBTOTAL in Filtered Tables

If you’re working with filtered tables and want subtotals for visible rows based on a condition, try this approach

Steps

  1. Filter your dataset based on any condition.

  2. Use this formula

=SUBTOTAL(109, FILTER(B2B, A2A = "Gadgets"))

The function code 109 ignores manually hidden rows and works well with filters. This ensures your subtotal only reflects visible rows in the filtered result that match the category "Gadgets".

Why Use SUBTOTAL Instead of SUM?

The key advantage of SUBTOTAL is that it automatically excludes rows hidden by filter. This makes it better for reports that need to update dynamically when users apply filters.

For example

  • SUM includes all rows visible or not.

  • SUBTOTAL adjusts to what’s shown, keeping the report accurate.

Limitations of SUBTOTAL with Condition

  • You can’t use logical operators (>, <, etc.) directly inside SUBTOTAL.

  • Complex conditional logic requires helper columns or additional formulas.

  • SUBTOTAL only works vertically, not across rows horizontally.

Despite these limitations, you can achieve powerful results with a little creativity.

Practical Use Case Sales Dashboard Example

Let’s say you have a sales report with

  • Column A Region

  • Column B Sales amount

  • Column C Product type

You want to see total sales for “South region and “Electronics category only when those rows are visible.

Here’s how

  1. In cell D1 write "South"

  2. In cell E1 write "Electronics"

  3. Use this formula

=SUBTOTAL(109, FILTER(B2B, A2A=D1, C2C=E1))

Now, whenever you filter the data, the subtotal will update automatically and reflect only the visible rows that match both conditions.

Alternative Use Pivot Tables for Conditional Totals

If your dataset is large and constantly changing, consider using pivot tables.

They allow you to

  • Group data by category or region

  • Add filters

  • Show subtotals automatically

While you won’t be using SUBTOTAL directly, pivot tables provide a user-friendly interface for summarizing conditional data.

Creating conditional subtotals in Google Sheets may require more steps than Excel, but with tools like FILTER, ARRAYFORMULA, and SUBTOTAL, you can achieve the same results effectively. Whether you’re building interactive reports or cleaning up data for analysis, these methods will help you create smarter, more dynamic spreadsheets.

Understanding how SUBTOTAL works with conditions will give you more control over your data and allow you to deliver insights with confidence.