Mastering the FILTER Function in Google Sheets from A to Z

Buzz

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

Content

What is the FILTER function used for?

How to Use the FILTER Function in Google Sheets

Using the FILTER function to apply multiple conditions

Nesting multiple FILTER functions

Referencing multiple columns in FILTER function

Referencing cells in the condition part of the FILTER function

Combining SORT and FILTER functions

Some data types supported by FILTER function for filtering

Combine with COUNT function

Combine with SUM function

Filter by numeric condition

Filter by text condition

Filter by date

The FILTER function in Google Sheets is an incredibly powerful tool that allows you to filter data based on various criteria. If you're struggling to understand how to use this function, check out this guide on using the FILTER function in Excel, Google Sheets, and combining it with the UNIQUE function below!

What is the FILTER function used for?

The FILTER function in Google Sheets is a powerful tool for filtering data based on specific conditions. This function will take your data set and only return (i.e., display to you) the rows of data that meet the criteria you specify (e.g., only the rows corresponding to Customer A).This helps users display only the rows of data that meet 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:Given the data table

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

Question: Find the products that Lan has purchased.Procedure: Identify the Data Range as the column containing product values, i.e., 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 to manage and analyze 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 apply multiple conditions

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

Entering multiple conditions with the FILTER functionExample: Filtering information of students named “MALE” in the class including: Name, age, hometown, exam scores. Where: A1:E16 is the data range. Condition B1:B16='MALE”By using the basic data above, I can display information about the name, year of birth, hometown, and exam scores of students named “MALE” in the class.Excel is a useful tool for work and study, but not every device supports this software by default. To meet the demand for using Excel on all devices, Microsoft provides various versions at affordable 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] for the second FILTER function.Example: We use the data from the previous example, use the first FILTER function to filter values 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 review the example of the first FILTER function in this article to visualize it easily.

Returned results

Referencing multiple columns in 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 FILTER function to reference columns

Referencing cells in the condition part of the FILTER function

Instead of encoding 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 analysis tool to complete the FILTER function.Filtering products with values greater than cell E1. Therefore, we have the condition A1:A20>E1. =FILTER(A1:A10;A1:A10>E1) as shown in the figure.

Using FILTER function to reference cells

Combining SORT and FILTER functions

SORT function is used to sort the rows of a given range of cells based on the value(s) of one or more columns. SORT function assists users in sorting data in ascending or descending order.To combine SORT with FILTER, we use the formula=SORT(FILTER(range; condition1; [condition2; …]); sort_column; ascending_order; [sort_column2; …]; [ascending_order2; …])

FILTER(range; condition1; [condition2; …]): The filtered data range to be sorted.
sort_column: The index of the data column or a column outside the range containing 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 data table consisting of PRODUCT, SUPPLIER, QUANTITY fields. This data table will be used for the examples below:

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

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

Enter FILTER function combined with SORT

Some data types supported by FILTER function for filtering

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

Combine with COUNT function

Consider the following data table to perform the task of combining with COUNT function, combining with SUM function, combining with SORT function, and combining with 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 see the condition must be for the month of May and combine with the COUNT function to count the number of days.Formula:=COUNT(FILTER(B2:B7;MONTH(A2:A7)=5))

Combine FILTER function with COUNT

Combine with SUM function

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

FILTER function combined with SUM

Filter by numeric 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)

Filter numeric condition using FILTER function

Filter by text condition

Condition to filter is the product is Dog.We observe that the condition Dog is a text-based condition.Formula:=FILTER(A2:B7;C2:C7='Cat')Note: When filtering based on text conditions, ensure to enclose the condition in double quotation marks and input accurately to avoid errors.

Filter text condition using FILTER function

Filter by date

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

Filtering time using the FILTER functionThus, this article has guided you on how to use the FILTER function in Excel, Google Sheets, and how to combine UNIQUE with FILTER. Hopefully, you will successfully apply them to effectively serve your work and study.

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]