FILTER Function to Extract Data

Hi,

In the source_data sheet I have sample sales and profit growth (%) data at the report date and at a previous period (t-1) over 1 year and 3 years.

In the summary_data sheet I would like to extract from the source_data sheet the Product Code, Product Name, Country, Sales and Growth numbers for products in which the previous growth value was negative and the current value was positive for each period (1 year and 3 years), or vice-versa.

For example, Product AAA had previous growth of 2.00% and current growth of -1.00% over 1 year. EEE had previous growth of -1.00% and current growth of 2.00% over 3 years. Both of these products are therefore included in the summary_data sheet.

Someone kindly suggested using the FILTER function:

=FILTER(IF(LEFT(A1:K1)="1",IF((source_data!F2:F16=2),source_data!A2:K16,""),IF(LEFT(A1:K1)="3",IF(source_data!K2:K16=2,source_data!A2:K16,""),source_data!A2:K16)),(source_data!F2:F16=2)+(source_data!K2:K16=2))

This formula gets that result that I expected with the sample data but doesn't seem to work if, for example, I change the the value in F2 from 2 to 3: using the criteria mentioned above, Product AAA should still be included in the results because 1 year growth at t-1 was positive and that at t was negative.

The FILTER() seems to be an elegant solution so could someone please suggest any amendment to make it work as expected?

Thanks!