Complete Guide to Using the FILTER Function in Google Sheets

Buzz

Ngày cập nhật gần nhất: 15/4/2026

Content

What is the FILTER function used for in Google Sheets?

How to Use the FILTER Function in Google Sheets

Using the FILTER function to input multiple conditions

Nesting multiple FILTER functions

Referencing multiple columns in the FILTER function

Referencing cells in the condition of the FILTER function

Combining the SORT and FILTER functions

Some data types supported by the FILTER function for filtering

Combining with the COUNT function

Combining with the SUM function

Filtering by numerical condition

Filtering by text condition

Filtering by date/month/year condition

The FILTER function in Google Sheets is a powerful tool for filtering data based on various criteria. If you're unsure how to use this function, check out this article on using the FILTER function in Excel, Google Sheets, and combining it with the Unique function!

What is the FILTER function used for in Google Sheets?

The FILTER function in Google Sheets is a powerful tool for filtering data based on specific conditions. This function will take your dataset and only return (display for you) the rows of data that meet the criteria you specify (e.g., only rows corresponding to Customer A).This helps users display only the rows of data that satisfy specific criteria, making data analysis and understanding easier.

Purpose of using the FILTER function in Google SheetsSyntax =FILTER('range of values','condition 1';[' condition 2';..]), where:

Range of values: Data to be filtered on the Google Sheet.
- Condition 1: Condition 1 is the column or row containing true or false values corresponding to the first column or row of the data to be filtered.
- Condition 2…: Condition 2 is additional conditions with the data to be filtered.

Applications of the FILTER function:

Filtering a range of data based on criteria you specify.
Used to return multiple criteria.
Used to return multiple criteria and sort.

Illustrative Example:Consider the following dataset

Tên	Sản phẩm
Hoa	Viết
Lan	Thước
Cúc	Tập
Cúc	Sách
Lan	Bút Chì
Hoa	Màu Nước
Lan	Máy Tính

Prompt: Find the products that Lan has purchased.Procedure: Identify the Data Range as the column containing product values, namely the Product column => value range from B2:B8. Set the condition as Name is Lan, so we will check the Name column, condition A2:A8='Lan'.Formula: =FILTER(B2:B8;A2:A8='Lan')

How to Use the FILTER Function in Google Sheets

Are you struggling with managing and analyzing data in Google Sheets? Do you want to find a tool to quickly and effectively filter data? The FILTER function is the perfect solution for you! Here's the simplest way to use the FILTER function in Google Sheets:

Using the FILTER function to input multiple conditions

The FILTER function in Google Sheets is used to conditionally filter data in Excel with the following syntax:=FILTER(A1:E16,B1:B16='MALE”

Applying multiple conditions with the FILTER functionExample: Filtering information of students named “MALE” in the class including: Name, age, hometown, test score. Where: A1:E16 is the data range. Condition B1:B16=”MALE”By using the basic data above, I can display information about the name, birth year, hometown, and test scores of students named “MALE” in the class.Excel is a useful tool for work and study, but not all devices support this software by default. To meet the demand for using Excel on any device, Microsoft provides various versions with suitable prices such as:

Nesting multiple FILTER functions

Function structure: =FILTER(FILTER(range;condition);condition)By using the output of your first FILTER function as the [range of values] of the second FILTER function.Example: We use the data from the example above, using the first FILTER function to filter data greater than 100 and the second FILTER function to filter values greater than 400.Step 1: Select any cell in the data table > Enter the formula =FILTER(FILTER(A1:A10;A1:A10>100);A1:A6>400)

Enter the FILTER function to nest multiple valuesThe result will return the filtered values that the function 'FILTER(A1:A10;A1:A10>100)' will serve as the 'range of values' for the second function. Please refer back to the example of the first FILTER function in this article for better understanding.

The returned result

Referencing multiple columns in the FILTER function

The FILTER function in Google Sheets allows you to filter data based on conditions and return values that meet those conditions. In case you want to reference multiple columns for the FILTER function, you can use the following syntax:=FILTER(A1:B20,A1:A20 > 50,B1:B20 > 50)

Using the FILTER function to reference columns

Referencing cells in the condition of the FILTER function

Instead of hardcoding a value in [condition 1], you can simply reference another cell containing the condition to be checked. This way, you can easily change the condition to be checked or use other parts of the spreadsheet analytics to make the FILTER function complete.Filtering products with values greater than cell E1. Hence we have the condition A1:A20>E1. =FILTER(A1:A10;A1:A10>E1) as shown in the figure

Using the FILTER function to reference cells

Combining the SORT and FILTER functions

The SORT function is used to sort the rows of a given range of cells based on values that can be from one or more columns. The SORT function helps users arrange data in ascending or descending order.To combine the SORT function in FILTER, we have the formula=SORT(FILTER(range; condition1; [condition2; ...]); sort_column; ascending_order; [sort_column2; ...]; [ascending_order2; ...])

FILTER(range; condition1; [condition2; ...]): The filtered data range to sort.
sort_column: The index of the data column or a column outside the range containing the values you want to sort.
ascending_order: TRUE to sort sort_column in ascending order. FALSE to sort sort_column in descending order.
[sort_column2; ...]; [ascending_order2; ...]: Similar to sort_column and ascending_order arguments.

We have the following example:Consider the following table of PRODUCT, SUPPLIER, and QUANTITY fields. This data table will be used for the examples below:

Data table of productsUtilize SORT and FILTER functions to filter products by supplier and sort them in descending order.=SORT(FILTER(A2:A15;C2:C15=F1);1;FALSE)

FILTER(A2:A15;C2:C15=F1): Filter products by supplier.
1: Data column to sort is the product column.
FALSE: Sort in descending order.

Enter the FILTER combined with SORT function

Some data types supported by the FILTER function for filtering

The FILTER function is an incredibly powerful tool in Google Sheets that helps you filter data based on various criteria. This function can be used to filter data of different data types, including:

Combining with the COUNT function

Consider the following dataset to execute the task of combining with the COUNT function, along with the SUM function, the SORT function, and combining multiple conditions.

Date	Value	Product
27/05/2021	333	Dog
28/05/2021	100	Cat
29/05/2021	400	Dog
30/05/2021	500	Tiger
06/06/2021	700	Cat
01/06/2021	100	Dog

Task: Count the number of days in May.Thus, we observe that the condition must be the month of May, combined with the COUNT function to count the number of days.Formula:=COUNT(FILTER(B2:B7;MONTH(A2:A7)=5))

Combining FILTER with COUNT function

Combining with the SUM function

Task: Calculate the total value for the month of June.Perform the condition for days in June and combine it with the SUM function to calculate the total.Formula:=SUM(FILTER(B2:B7;MONTH(A2:A7)=6))

Using FILTER combined with SUM function

Filtering by numerical condition

Task: Filter products with a value of 400.The condition to filter is the value equals 400.Formula:=FILTER(A2:B7;B2:B7=A10)

Filtering numerical conditions using FILTER function

Filtering by text condition

Condition to filter is the product being Dog.We see that the condition Dog is a text-based condition.Formula:=FILTER(A2:B7;C2:C7='Cat')Note: When filtering by text condition, make sure to enclose the condition in double quotes and enter it accurately to avoid errors.

Filtering text conditions using FILTER function

Filtering by date/month/year condition

Task: Filter products with the date as 30/5/2021.We observe that the condition is in the date format.Formula:=FILTER(A2:B7;DATE('2021';'05';'30')=A2:A7)

Filtering time using FILTER functionSo, this article has guided you on how to use the FILTER function in Excel, Google Sheets, and how to combine unique with FILTER. Hope you will successfully apply it, serving effectively in your work and studies.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]