How to use the SORT function with the FILTER function.
Watch the video below:
In the last video we learnt how to use the FILTER function. We used a simple criteria to analyze our data. Next we used multiple criteria to analyze the same data to extract more specific information.
The formula below allowed us to analyze our data based on the product ‘Laptop’ criteria: FILTER(A5:D30,(C5:C30=G2),””)
Range A5:D30 represents our ARRAY of data.
Range C5:C30 represents the data in the Product column.
G2 = Laptop and represents the criteria
Quotes “” represent a BLANK if no data is found (if_empty)
The Filter function using the multiple criteria on the same data gives the following result: =FILTER(A5:D30,(C5:C30=G2)*(A5:A30=J2),””)
Range A5:D30 represents our ARRAY of data.
Range C5:C30 represents the data in the Product column.
G2 = Laptop and represents the criteria
* represents AND
Range A5:A30 represents the data in the Region column
Range J2 has West as value and represents the second criteria
Quotes “” represent a BLANK if no data is found (if_empty)
Next we use the SORT function to sort the Units Sold in in column 4 in descending order (-1):
=SORT(FILTER(A5:D30,(C5:C30=G2)*(A5:A30=J2),””),4,-1)
How do we sort the filtered data in ascending order. We use the following formula:
=SORT(FILTER(A5:D30,(C5:C30=G2)*(A5:A30=J2),””),4,1)
