Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to filter quarter 1 and 2 transaction in Microsoft Excel using the FILTER function. Let’s get started.
Video
Dataset
The screenshot below shows the transaction data we are going to be working with. The data is officially formatted as an Excel Table and named data
FILTER Quarter 1 and 2 Transactions and Return Specific Columns
To filter the data based on the two criteria and return the following columns: Date, Products, Sales:
- Execute this formula in cell H2:=FILTER(FILTER(data,data[Sales]>=10000),COUNTIFS(H1:J1,data[#Headers]))
- Click Enter to commit into the Excel
Explanation:
- The FILTER(data, data[Sales] >= 10000) filters the rows from the data table where the value in the Sales column is greater than or equal to 10,000.
- The COUNTIFS(H1:J1, data[#Headers]) counts how many times the values in cells H1 to J1 match the values in the headers (column names) of the data table. In order words, the COUNTIFS(H1:J1, data[#Headers]) essentially count how many columns in the data table have headers that match the values in H1, I1, and J1.
- The outermost FILTER function then takes the result of the inner FILTER and applies a filter again. It filters the rows from the previously filtered data where the count of matching headers (from step 2) is not zero. In other words, it selects rows where there is at least one column with a header that matches the values in H1, I1, or J1.
From the screenshot below, we returned the three columns and the interesting thing about this is that the formula is completely dynamic in the sense that if we replace Products with Payment Type in cell 1i, the formula will update. In addition, the formula spilled to the neighbouring rows and columns and that eliminate the need to use the fill handle to drag down and across
See you in the next tutorial