HomeGoogle DocsSpreadsheetSUMIF Excluding Hidden Rows in Google Sheets

SUMIF Excluding Hidden Rows in Google Sheets [Without Helper Column]

Published on

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.

sample data - sumif without hidden rows

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?

subtotal with condition or subtotalif

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.

exclude hidden rows with helper column in SUMIF

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.

Create a Virtual Helper Column to Use Inside Formula

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.

  1. Consume lots of time to create the virtual helper column.
  2. 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.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

3 COMMENTS

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here