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.
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.
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.
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.
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.
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.
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.
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.