The QUERY function in Google Sheets can’t identify hidden rows. However, you can select only visible rows using the QUERY function with an easy workaround in Google Sheets.

This means when you use a QUERY formula on already filtered data (or data containing hidden rows), it will only pull the visible rows. This is not possible in the usual case!

In this tutorial, you will learn how to add this powerful feature to a QUERY formula using a workaround approach. For this purpose, we will use a virtual helper column (an additional column that is not physically available in the dataset).

Virtual Helper Column Formula for QUERY Use

Hidden rows can be a nightmare for many spreadsheet users as they can produce unwanted results. Several powerful functions, such as SUMIF, QUERY, and SUMPRODUCT, fall short of handling hidden or filtered-out rows.

The only effective way to deal with hidden rows is by using the SUBTOTAL function and its associated function numbers.

However, the SUBTOTAL function has a limitation: it doesn’t expand automatically with an ARRAYFORMULA, which reduces its utility for functions that utilize array arguments, such as QUERY.

In our virtual helper column concept, we will of course use the SUBTOTAL function, but to expand it, we will use a Lambda helper function. Let’s dive into it.

Steps:

We have our sample data in A1:D, which contains item names, names of the salespersons, sales quantity, and sales amount in columns A, B, C, and D, respectively.

We will hide 2-3 rows and see how to omit those rows while manipulating this data using the QUERY function.

1. Select a Non-Blank Column:

Consider any column in the range that doesn’t contain blank cells. For example, we will consider column A here (there might be blank rows at the end, but that is not an issue).

2. Starting Point:

The data starts at row #1. So, in cell E1, enter the following formula:

=SUBTOTAL(103, A1)

This returns the count of values in cell A1, which will be 1. Next, we’ll convert it into a custom lambda function and expand it using a lambda helper function.

3. Create a Custom Formula Using LAMBDA:

First, create a custom formula using the LAMBDA function:

=LAMBDA(r, SUBTOTAL(103, r))

It might return an #N/A error in cell E1, which you can ignore. This formula follows the syntax LAMBDA([name, …], formula_expression), where ‘r’ is the name to be used in the formula expression, which is the SUBTOTAL formula.

4. Use the Custom Function with the MAP Function:

Use this custom function within the MAP lambda helper function as follows:

=MAP(A1:A, LAMBDA(r, SUBTOTAL(103, r)))

This follows the syntax: MAP(array1, [array2, …], lambda)

This formula in cell E1 will create a physical helper column in E1:E. However, when using QUERY, we will horizontally append this formula with the ‘data’ to avoid using E1:E, the physical helper column.

So the ‘data’ in the QUERY formula will be: {A1:D, MAP(A1:A, LAMBDA(r, SUBTOTAL(103, r)))}

Let’s see how to omit hidden rows in QUERY in the examples below. Before that, let me shed some light on the logic.

The Logic Behind Including Only Visible Rows in the QUERY Function

When you apply Data > Create a filter and filter out any row, manually hide any row, or hide rows by any means such as applying a Slicer or row grouping, the virtual helper column will have the value 0 in those rows. In all other rows with content in column A, the values will be 1.

So, in the QUERY function WHERE clause, we will apply a condition such as "WHERE Col5 = 1" which means the row is visible.

Note: We are using the range A1:D, which contains 4 columns, so the virtual helper column will be the fifth column.

This is the logic behind excluding hidden rows in the QUERY function in Google Sheets.

Example of Excluding Hidden Rows in the QUERY Function

Here are a couple of formulas that demonstrate how to handle hidden rows in Google Sheets QUERY:

=QUERY({A1:D, MAP(A1:A, LAMBDA(r, SUBTOTAL(103, r)))}, "SELECT Col1, Col2, Col3, Col4 WHERE Col5=1", 1)

This formula returns columns 1 to 4 where the fifth column (Col5) is equal to 1, representing visible rows.

=QUERY({A1:D, MAP(A1:A, LAMBDA(r, SUBTOTAL(103, r)))}, "SELECT Col1, SUM(Col4) WHERE Col5=1 GROUP BY Col1", 1)

This formula selects the first column (Col1) and calculates the sum of the fourth column (Col4). It includes only rows where the fifth column (Col5) is equal to 1, indicating visible rows. The results are grouped by the values in the first column.

Resources

• How to Omit Hidden or Filtered Out Values in Sum
• SUMIF Excluding Hidden Rows in Google Sheets
• Vlookup Skips Hidden Rows in Google Sheets
• Insert Sequential Numbers Skipping Hidden | Filtered Rows in Google Sheets
• COUNTIF | COUNTIFS Excluding Hidden Rows in Google Sheets
• Find the Average of Visible Rows in Google Sheets
• Count Unique Values in Visible Rows in Google Sheets
• IMPORTRANGE to Import Visible Rows in Google Sheets
• XLOOKUP Visible (Filtered) Data in Google Sheets
• XMATCH Visible Rows in Google Sheets
• Weighted Average of Filtered (Visible) Data in Google Sheets
• UNIQUE Function in Visible Rows in Google Sheets