Filtering by date

The FILTER function can also contain multiple criteria, and if we want to consider them all at the same time, they must be in parentheses, separated by an asterisk. This is a convenient way to filter sales results by date, and set the criterion by changing the year and month values ​​from the drop-down list we have created by using Data Validation option.

Let’s start with the table that contains the DATE, CITY, and SALES columns. The first column contains the dates of the transactions, in the second cities where the sale was made, and in the third the quantities that were sold.

Initially let’s create lists based on which, with the help of Data Validation, we will make a selection of criteria. Write two following formulas:

=UNIQUE(YEAR(A2:A99))

=UNIQUE(MONTH(A2:A99))

These formulas will allocate years and months from dates within column A, which are part of existing transactions. Further we’ll adjust F1 and F2 in such way that values are being chosen ​​from the drop-down lists based on the previously generated years and months.

Our goal is to return sales data for the selected date and year. Therefore, as a header, we will enter CITY and SALES values ​​into cells F5 and E5. Finally, E5 should contain a formula:

=FILTER(B2:C99,(MONTH(A2:A99)=F2)*(YEAR(A2:A99)=F1),”No value found”)

This formula returns the values ​​of the CITY and SALES columns, based on the year and month in the date column corresponding to the values ​​selected in the list.