The FILTER function in Google Sheets allows you to filter data and return rows that meet specific criteria. Below is how to use the FILTER function in Google Sheets .
Overview of the Filter function formula in Google Sheets
By using this Google Sheets function, you can get multiple matches. Because the data is filtered using the function, the results will be updated automatically.
The Google Sheets Filter function formula includes: Range - Range, Condition 1 - Condition 1, and [Condition 2…]. The first two factors are mandatory, while the second and subsequent conditions are optional. Google Sheets does not limit the number of conditions in the Filter function.
=filter(range, condition 1, [condition 2]…)Example of using the Filter function in Google Sheets
You can find information about sales and profits of each item in different states in the US when using the Filter function in Google Sheets. When a column containing the state name is condition 1, you can create a drop-down list for it.
Create drop-down lists for test conditions
1. Right-click a cell to open the drop-down menu.
2. Scroll down and go to the Data validation section .
3. In the data validation section, set the criteria to List from a range.
4. Click the squares in the criteria section to select a range or formula.
5. In this case, the States column will be the range. You need to select items from cells A2 to A13 here.
6. Save the Data Validation settings by clicking Save .
This action will create a drop-down menu containing items specific to the selected range.
Now that you have created the drop-down menu, start filtering Grocery Items, Sales and Profit by States using the Filter function on Google Sheets.
Implement the Filter function
Here the range is data in 3 columns: Sales, Grocery items and Profit. Now select cells B2 to D13 in the first argument of the Filter function.
Suppose you want to add more data to the column selected as a range. Remember the second value in the open range. You can do this by deleting the number of cells, here it is 13.
As soon as a new value is added, the Filter function will calculate these values without requiring you to filter them again.
The condition value in this example is the States column. Select the States column and set the condition that that column = with the data in cell G5.
In this example, Range Area B2:D has the same height as condition area A2:A.
After pressing Enter to deploy the formula, it will filter the data in the Grocery items, Sales, Profits columns according to the given conditions.
Also, note that the Google Sheets Filter function only includes valid values. If the value checks true, its value in the array is considered, otherwise the function ignores it and moves on to the next step.
In this example, when you select Utah from the drop-down list, you'll see the related data in the filter area.
Filter data by checking multiple conditions
Now we'll update the Google Sheets Filter function to include values greater than 5000 as the second condition. To filter data that meets both conditions, you need to add a new condition to the filter formula.
1. Go to cell F8 where you added the formula earlier.
2. Select the Profit column as the range for the second condition, the new formula is as follows:
=Filter(B2:D,A2:A=G5,D2:D>5000)
3. Press Enter to execute the formula.
The profit from selling flour in Utah is 1240, which does not meet the second condition, so the Filter function of Google Sheets ignored it. Similarly, you can filter this data by trying more conditions.
Above is how to use the Filter function in Google Sheets . Hope the article is useful to you.
Google One AI Premium is offering a 1-month free trial for users to sign up and experience many upgraded features such as the Gemini Advanced assistant.
Starting with iOS 18.4, Apple allows users to decide whether to show recent searches in Safari.
Discover how professional sports teams use data analytics to boost revenue, improve performance, and create innovative income streams in modern athletics.
Windows 11 has integrated the Clipchamp video editor on Snipping Tool so that users can edit videos as they want, without having to use other applications.
Funnel charts typically depict how different stages merge into a smaller output. Here's how to create a funnel chart in PowerPoint.
The Clipboard tool on iPhone will store images or data. However, when you save too much information, it can affect your privacy. If so, you can set a shortcut to clear the clipboard.
To ensure children's safety when using the phone, parents can create an Apple ID account for their children through the Family Sharing feature on iPhone.
When chatting via direct message on Instagram, sometimes we accidentally send the wrong message, we can recall the Instagram message before the other person reads the content so that no one knows.
When you update to iOS 18 beta 5, you will be able to use the feature of hiding ads in Safari to avoid distraction when reading web content.
Google Gemini now allows users to share their conversations with others via a public link similar to sharing chats on ChatGPT.
The Microsoft Rewards program rewards users with points they earn for offers from brands they partner with. Here's one way you can earn Microsoft Rewards points.
Aya is a support champion with a rather annoying playstyle and is often banned in ranked matches. However, if you have the opportunity to bring Aya into ranked matches, this article will help you grasp the formula for playing Aya effectively.
To make it easier to find information on Metro Line 1, users should install the Metro HCMC application to get information about the stations they need to go to and ticket prices for each route.
Cydia has become very familiar to users of jailbroken iOS devices. It is a third-party application store that provides users with applications (free and paid) that deeply intervene in the system so that iOS devices can be used more flexibly according to the user's preferences.
Just like the Gmail app, the Appe Mail app also has the option to send emails with file attachments to support users in the email sending process.
