By using the Query function, we can conditionally filter the last N rows in a ‘data’ range in Google Sheets. With the help of the WHERE and OFFSET clauses in the Query function, we can apply the required condition to filter the data and offset a certain number of rows from the front to get the last N rows.
Real-Life Use of Conditionally Filtering Last N Rows in Google Sheets
Let’s assume you have a list of teams and their scores from the last 10 tournaments. To find the sum, average, maximum, and minimum of the last N results (scores) for a team, you can use the dynamic last N row filtering method that also applies conditional filtering.
Query Formula to Conditionally Filter Last N Rows in Google Sheets
To get started, create a sample data table by typing the values in columns B and C on your sheet. Then, type the filter condition/criterion in cell E2 and the number of last N rows you want to filter in F2.
Now, let’s learn how to conditionally filter the last N rows in Google Sheets.
Apply Condition Using WHERE in Query
Conditional filtering is easy with the Query function. In this case, we only want to filter the data in the range B2:C, if the name in B2:B matches the criterion in cell E2, i.e., “John”.
Here is the formula for that:
=query(B2:C,"Select * where B='John'")You can also use a cell reference for the criterion within the formula:
=query(B2:C,"Select * where B='"&E2&"'")Result:
COUNTIF in Query OFFSET to Filter Last N Rows in Google Sheets
After completing the first step of conditionally filtering the dataset, we now want to filter the last N rows from this output. There is no built-in clause in the Query syntax to filter or conditionally filter the last N rows in Google Sheets, so we need to write a formula for that.
We can make use of the OFFSET clause in a certain way for this purpose. To conditionally filter the last N rows, we can use the following logic:
=COUNTIF(B2:B,E2)The above COUNTIF formula returns the conditional count, which is the number of rows containing the criteria “John” in column B.
To find the number of rows to offset, use the formula:
=COUNTIF(B2:B,E2)-F2If the value in F2 is 1, the Countif formula would return 4. That means we need to offset 4 rows and return the last 1 row.
The next question is how to connect the above Countif in Query. For that, we can follow this approach:
Use the just above Query formula as the data in another Query and offset the rows using the above Countif formula.
Syntax to Conditionally Filter Last N Rows in Google Sheets:
=query(Query_formula,"Select * offset "&countif_formula)So here is the formula to conditionally filter the last N rows in Google Sheets:
=query(query(B2:C,"Select * where B='"&E2&"'"),"Select * offset "&countif(B2:B,E2)-F2)Errors:
The Query formula above would return a #VALUE! error in two cases:
- When the filter condition is not available in the data range. If the inner Query returns #N/A, the formula would return the said error.
- When the last N is greater than the number of rows in the filtered output.
Additionally, the formula would return a #N/A! error if the last N is 0 or blank.
That’s all about how to conditionally filter the last N rows using Query in Google Sheets.
Conditional SUM/AVERAGE/MAX/MIN Last N Results
Here are some real-life uses of the above type of last N row filtering. The following examples are based on the provided data.
How to Sum Last N Values Conditionally in Google Sheets?
Formula:
=SUM(query(query(B2:C,"Select * where B='"&E2&"'"),"Select Col2 offset "&countif(B2:B,E2)-F2))How to Max/Min Last N Values Conditionally in Google Sheets?
Formula:
=MAX(query(query(B2:C,"Select * where B='"&E2&"'"),"Select Col2 offset "&countif(B2:B,E2)-F2))How to Average Last N Values Conditionally in Google Sheets?
For the conditional average of last N values, replace MAX with AVERAGE.
Formula:
=AVERAGE(query(query(B2:C,"Select * where B='"&E2&"'"),"Select Col2 offset "&countif(B2:B,E2)-F2))Additional Resources
- Find the Average of the Last N Values in Google Sheets
- Address of the Last Non-Empty Cell Ignoring Blanks in Excel (Sheets also)
- How to Find the Last Row in Each Group in Google Sheets
- How to Find the Last Matching Value in Google Sheets
- Lookup Last Partial Occurrence in a List in Google Sheets
- Lookup to Find the Last Occurrence of Multiple Criteria in Google Sheets
- Vlookup Last Record in Each Group in Google Sheets
- How to Find the Last Value in Each Row in Google Sheets
- Consolidate Only the Last Row in Multiple Sheets in Google Sheets
- Find the Last Non-Empty Column in a Row in Google Sheets
- Count From First Non-Blank Cell to Last Non-Blank Cell in a Row in Google Sheets
Remember, by using the Query function in Google Sheets, you can dynamically conditionally filter the last N rows based on specific criteria. This opens up a world of possibilities for data analysis and reporting.
