Welcome To Excel Avon
FILTER Formula in Excel
The Excel FILTER function allows you to FILTER a range of data based on criteria that you define and extracts matching records. The results from FILTER are dynamic. When values in the source data change, or the source data array is resized, the results from FILTER will update automatically. FILTER can work with both vertical and horizontal arrays.
Formula
=FILTER (array, include, [if_empty])
Arguments
array – The range or array of values you want to filter.
include – Boolean array, supplied as criteria.
if_empty – [optional] Value to return when no results are returned.
Type of FILTER Formula
DOWNLOAD USED EXCEL FILE FROM HERE>>
There are three types of examples which are single, multiple, and date examples.
Single criteria
As you can see, we have created some data given in three columns name, age & gender. Suppose a single criterion is given. Single criteria should be such that find the data of all the people of 20 years. So now we will use the filter formula.
According to the criteria I will apply the filter formula.
=FILTER(Table1,Table1[Age]>=20)
Data of all people of 20 years or 20 years will be found.
Multiple Criteria
In Multiple Criteria, more than one Criteria is given, such as women aged 50 or more than 50 years have to be known from any data, but there are 50 more women Criteria.
As in the given example, we have given data in which the age of females and male and all are given and we have to find all the females in the data sheet who are more than 30 years of age.
We will apply FILTER formula after considering the multiple criteria.
=FILTER(Table13,(Table13[Age]>=30)*(Table13[Gender]="Female"))
Only two women in the data sheet were over the age of 30.
Date Filter
The date filter can be used for a fixed date such as the number of people born in January or the number of UID cardholders in March 2012
We have made some data as an example, in which we need the data of all those women and men who were born in the month of July.
We will apply FILTER formula after considering the date criteria.
=FILTER(Table134,MONTH(Table134[DOB])=7)
And we will get the data of all the people who were born in July.
DOWNLOAD USED EXCEL FILE FROM HERE>>
So I hope you have understood this formula and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
You can also see well-explained video here
