Subtotal with Condition in Google Sheets [Step by Step Guide]

0
81
Subtotal with Condition in Google Sheets

You can do Subtotal with Condition in Google Sheets. Subtotal is the only function that can have an affect on filtered or hidden data. But users are unaware how to use Subtotal function with Conditions. In this post we can learn that. Before that just go through the following resources.

1. How to Use Subtotal Function in Google Sheets [Omit Hidden or Filtered Out Values]

2. Lean about All Subtotal Functions Numbers in Google Sheets.

The above links are for those who are not familiar with Google Sheets Subtotal function and its essential function numbers. Now back to our tutorial.

How to Use Subtotal with Condition in Google Sheets

I took a new approach here to explain the use. Let me try to explain the tips with the help of few screenshots and narration.

As you know when you have hidden rows, which to be excluded from the total, you can use Subtotal formula in Google Sheets as below.

subtotal normal use with hidden rows

For this purpose, you can use Google Sheets function number 109 with Subtotal. This is applicable to a filtered data also where the above function will only add values in the visible rows. Now how to use Subtotal with condition in Google Sheets.

sumif normal use with hidden rows

Here I want find the Sales Amount of Item Apple. We can’t use Subtotal here as Subtotal won’t accept conditions. So I used a Sumif function.

Learn the use of Sumif in Google Sheets [Simplified the Use]

Here the problem is Sumif won’t work with hidden rows or on a filtered data. So we should adopt a different approach. We should master the technique of combining Subtotal with Sumif. So while one function works with visible rows the other can handle condition. How the combination works and how to do that? There are two ways to do this.

Subtotal with condition – Simple approach and suggested to use when you have more than 15 rows.

subtotal and sumif combination with additional column

Here we added a new column to our above data and there used Subtotal function. This formula populates Column C values in Column D. Once the formula applied, you can hide this column D as it’s not part of our original data. Now instead of Sumif we used Sumifs as we want to check two conditions.

Learn how to use Google Sheets SUMIFS function.

Here we are checking two conditions that whether the Value in Column B is Apple and Value in Column D is >0. Column D is our new column with Subtotal function. When you hide any rows, or apply filter, the hidden row value in Colum D will become zero.

Subtotal with Condition with limited number of rows.

When you have limited number of rows, you can say 15 rows, without an additional column you can do subtotal with condition in Google Sheets. How?

The same above additional column we can include in the above Sumifs formula as an array.

Here is that formula.

=sumifs(C2:C5,B2:B5,”Apple”,{subtotal(109,C2);subtotal(109,C3);subtotal(109,C4);subtotal(109,C5)},”>0″)

Conclusion:

This way you can do Subtotal with Condition in Google Sheets. If you want to explore the possibilities of Subtotal with condition, there are two more tutorials.

1. SUMIF Excluding Hidden Rows in Google Sheets.

2. Google Sheets Query Hidden Row Handling.

LEAVE A REPLY

Please enter your comment!
Please enter your name here