Some times you may require to filter the next row to the filter criteria row in Google Sheets. I mean the condition to filter is in one row but the result we want is from the adjoining (below condition) row.

You may require to do it if your data is not presented similar to records we present in DB applications. I mean you haven’t well arranged your data under proper field labels.

Needless to say, we can effortlessly use the filter, aggregate, pivot, unique, chart like all the functionalities in Google Sheets in well-arranged datasets. Also, there are database-specific functions in Google Sheets for such datasets.

As I have already mentioned, some times we may want to present or keep our data in a specific manner. In such a case, we may face issues in manipulating our data.

A Very Basic Example to Properly Arranged Data:

A Very Basic Example to Improperly Arranged Data:

As an example, here in the below sheet (please refer to the image below), the criterion is “Supply”.

I want to extract the values in the next rows to this criterion. That means the values 25,000 and 15,000 from row numbers 7 and 17 in column C respectively.

Here are the step-by-step instructions to use FILTER to extract values from the rows below the conditions (criteria) row in Google Sheets.

Steps to Filter Next Row to the Filter Criteria Row in Google Sheets

There are three main steps. Here are them.

1. Filter row numbers matching the condition (step # 1).
2. Use step # 1 formula to find the next row numbers matching the condition (step # 2).
3. Use step # 2 formula result (row numbers) as criteria in a filter to filter the required final values (step # 3).

Find the above steps below (may not be titled exactly as per the above points).

Filter Row Numbers Matching the Condition (Step # 1)

First of all, we will filter the rows that match the criterion. For that, we can use row numbers as the Filter function criteria in Google Sheets.

=filter(
     row(C:C),
     C:C="Supply"
)

This formula will return the row numbers 6 and 16 that contain the conditions.

What we want is the values in the next row 7 and 17. I mean the values in the cells C7 and C17. What is the solution?

Filter Row Numbers Below the Matching Criteria Rows (Step # 2)

Let’s add the number 1 to the above-filtered result by modifying the above formula as below to get the required row numbers 7 and 17.

=ArrayFormula(
     filter(
        row(C:C),
        C:C="Supply"
     )+1
)

Here is the most important step to filter next row to the filter criteria row in Google Sheets.

Let’s filter column C if the row numbers are 7 and 17. How do we do that in Google Sheets?

Final Formula to Filter Next Row to the Filter Criteria Row (Step # 3)

Using Regexmatch we can make use of the above row numbers as the criteria within a Filter formula. It’s just a simple task!

Related: Regexmatch in Filter Criteria in Google Sheets [Examples].

Separate the row numbers 7 and 17 (above last formula result) with a Pipe sign. This is part of the process of making the row numbers suitable to use in Regexmatch as a regular expression.

The Textjoin function with the above formula will come in handy here.

=textjoin(
     "|",
     true,
     ArrayFormula(
        filter(
           row(C:C),C:C="Supply"
        )+1
     )
)

The output of the above Textjoin formula will be 7|17.

It is considered as a partial match regular expression, so would even match the rows 107, 117, etc. We should make it an exact match formula.

I mean we must use 7|17 as ^7$|^17$ for that.

Let’s modify the above formula to include those additional Caret and Dollar characters. Let’s call this new formula as regex_criteria.

="^"&textjoin(
     "$|^",
     true,
     ArrayFormula(
        filter(
           row(C:C),C:C="Supply"
        )+1
     )
)&"$"

Here is the Regexmatch to use as the filter criteria to filter the values in the next row to the criteria row in Google Sheets. Let’s call it filter_criteria.

filter_criteria Syntax:

=ArrayFormula(
regexmatch(row(C:C)&"",regex_criteria))

Formula (filter_criteria) as per the Syntax above:

=ArrayFormula(
     regexmatch(
        row(C:C)&"",
        "^"&textjoin("$|^",true,ArrayFormula(filter(row(C:C),C:C="Supply")+1))&"$"
     )
)

Then the final filter formula.

Syntax:

=filter(C:C,filter_criteria)

Formula as per the Syntax above:

=filter(
     C:C,
     regexmatch(
        row(C:C)&"",
        "^"&textjoin("$|^",true,filter(row(C:C),C:C="Supply")+1)&"$"
     )
)

Note: The ArrayFormula function is not a must within a Filter function based formula.

To sum the output, wrap the above filter formula with SUM.

Additional Tips

Here are a few more tips related to the above idea that are mainly about multiple criteria use.

With minor tweaks within the Filter formula (the one that we have used inside the Textjoin as a regular expression) we can include multiple criteria.

Assume I want to filter the rows next to the rows “Supply” and “testing & commissioning”. How to include these two conditions?

For including that, we should just change the Filter formula within the Textjoin (used as regular expression) as below.

Earlier:

filter(row(C:C),C:C="Supply")

After Making Changes:

filter(row(C:C),regexmatch(C:C,"Supply|testing & commissioning"))

So the formula will be;

=filter(
     C:C,
     regexmatch(
        row(C:C)&"",
        "^"&textjoin("$|^",true,filter(row(C:C),regexmatch(C:C,"Supply|testing & commissioning"))+1)&"$"
     )
)

The above multiple criteria filter formula is case sensitive. So if you use the criterion “supply” instead of “Supply”, the formula won’t match the “Supply” in cell C6.

So obviously the formula would fail to extract the value from the row next to this criteria row. This is the case with the other criteria “testing & commissioning”.

There is a quick fix to solve this case sensitivity issue. Use “(?!)” pattern before the criteria.

filter(row(C:C),regexmatch(C:C,"(?i)Supply|testing & commissioning"))

So the formula would be;

=filter(C:C,regexmatch(row(C:C)&"","^"&textjoin("$|^",true,filter(row(C:C),regexmatch(C:C,"(?i)Supply|testing & commissioning"))+1)&"$"))

That’s all about how to filter next row to the filter criteria Row in Google Sheets. Thanks for the stay, enjoy!