The SUMIF formula is useful for conditional SUM in Google Sheets. But do you know how to use the SUMIF excluding hidden rows in Google Sheets?
Google Sheets SUBTOTAL function can help you to deal with hidden rows. But it may not accept criteria or conditions.
So I am aiming for a SUMIF and SUBTOTAL combination.
Note:- Hidden rows in this context means rows that are hidden with filtering, row grouping, or manually hidden.
How to Sum a Column Based on Conditions and Excluding Hidden Rows
In Excel, I have seen users using a SUMPRODUCT, SUBTOTAL, ROW, and OFFSET combination to do the task.
But that combination may not work as desired in Google Sheets.
I have included the said Excel formula in my post titled Subtotal Function With Conditions in Excel and Google Sheets.
Find the use of Countif, Averageif, Minifs, Maxifs, and Sumif in Visible rows without helper columns in Excel there.
The helper column approach in Google Sheets is also detailed there. I hope you will find time to check that also.
The SUMIF Function That Deals with Hidden Rows – What’s It?
The purpose of SUMIF is to sum a range based on condition. But it will add values in hidden rows if the condition matches.
Sadly, there is no built-in function in Google Sheets or Excel to deal with hidden rows based on condition.
SUBTOTAL is the only function that deals with hidden rows properly. But there is no SUBTOTALIF function to apply conditions.
So what is the final solution?
We can use a SUMIF and SUBTOTAL combination to exclude hidden rows.
There are two methods we can adopt. In that, one uses a helper column, and the other doesn’t use a helper column.
In the non-helper column method, there are two approaches – one is dynamic, and the other is non-dynamic.
The dynamic one uses the BYROW function, whereas the non-dynamic one uses multiple SUBTOTAL formulas in a combined form.
SUMIFS Excluding Hidden Rows in Google Sheets Using a Helper Column
Helper column – An additional column for our calculation purpose.
I think SUMIFS would be better to use with SUBTOTAL and simple to read. So I am going to use it here.
Let’s begin with how to apply conditional sum excluding hidden rows.
Our actual sample data spread across the range B2:E7. Column A is our helper column and I will come to that later.
In this example, I want to sum the sales value of one of the items, i.e., “Coverall,” in column B.
That means total column E if column B values are equal to “Coverall.”
We can easily do it with a SUMIF function. But we want one more thing!
=sumif(B2:B7,"Coverall",E2:E7)
When we hide any row containing “Coverall,” the value in the hidden row should not be reflected in the total.
Steps
So here are the steps to use SUMIF Excluding Hidden Rows in Google Sheets.
In the helper column A, in cell A2, apply the below Subtotal formula and copy-paste it down until cell A7.
=subtotal(109,E2)
The above SUBTOTAL is the key. Do you know why?
It pulls all the values from column E to A.
When you hide any rows in the range, the subtotal formula in that row will return zero instead of the actual value.
With this feature of SUBTOTAL, we can omit hidden row values from our total with a formula like this.
=sumifs(E2:E7,A2:A7,">0",B2:B7,"Coverall")
It will return the sum of cell range E2:E7 if B2:B7=”Coverall” and A2:A7>0.
When you hide any row, the value in the corresponding cell in column A will turn to 0 (zero).
So the SUMIFS formula will exclude that row in the total.
Now hide any row containing the item “Coverall” and see the magic.
SUMIFS Excluding Hidden Rows Without Using a Helper Column
We have already learned the use of SUMIF Excluding Hidden Rows in Google Sheets with the helper column above. Now we can do it with a virtual helper column.
Please delete column A. So the criteria column (Item) will become A, and the value column (Amount) will become D (please refer to the image below).
1. Non-Dynamic Approach
It is our non-dynamic formula approach to SUMIF Excluding Hidden Rows in Google Sheets, without any helper column in use.
=sumifs(D2:D7,{subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)},">0",A2:A7,"Coverall")
This formula looks complicated, right? But in the real sense, it’s not so!
The formula within the Curly Brackets is a virtual helper column.
Steps (Virtual Helper Column)
First, we need a helper column containing the SUBTOTAL and let’s use E2:E7. We use this column for a temporary purpose and can delete it later.
Please refer to column E. I’ve copied the E2 formula to E3:E7.
Now hit the shortcut keys Ctrl+~
to show the formulas in the helper column.
Remove the ‘=’ sign from the formulas in the helper column.
Once again, hit the shortcut key Ctrl+~
.
Now, what we want to do is to use the following formula in any blank cell.
=join(";",E2:E7)
Copy its output and apply the paste value in the same cell. You can use Ctrl+C for copy and Alt+E+S+V for paste as value.
It will look as follows.
subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)
Just place an open and close Curly Braces with the above text to make it an array.
{subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)}
Now you can delete helper column E.
2. Dynamic Approach Using the BYROW Function
The above non-dynamic approach has two main drawbacks.
- Consume lots of time to create the virtual helper column.
- Useful only when the number of rows in the source data is small, say <15.
We can overcome it by using the BYROW, a LAMBDA helper function, in Google Sheets.
=byrow(D2:D7,lambda(helper,subtotal(109,helper)))
We can replace the above virtual helper column formula with this BYROW in the SUMIFS, and here you go!
=sumifs(D2:D7,byrow(D2:D7,lambda(helper,subtotal(109,helper))),">0",A2:A7,"Coverall")
The above is my suggested formula for SUMIF Excluding Hidden Rows in Google Sheets.
This is amazing! but it won’t really work due to the dynamic nature of my sheet.
Is there a workaround to have virtual subtotal rows added dynamically as rows are added?
Nope!
Thank you very much, Prashanth. You are amazing! Thanks a lot for your help