Dynamic Array (FILTER) & expanding formulas

Hello Everyone,

I'm currently trying to build something that will do the following:

1. Use FILTER function to have a dynamic array based on 2 criteria
2. Once the array has been produced by the FILTER function, I want SUMIFS, XLOOKUP or IFS formulas to run based on the values derived from the FILTER function.

The problem I have is that the 2 criteria will invariably return results in various length - could be 5 rows, could be 100, or even more. The number will fluctuate (column K:L), but regardless of the number of rows returned, I want the aforementioned formulas to go in sync with the number of rows produced from FILTER.

I tried building the FILTER function in a Table, but this produces the SPILL error - the table doesn't expand automatically.

The whole trick is to avoid creating a Pivot Table or writing VBA. I have attached a photo of an example of what I have in mind.

FILTER EXAMPLE.jpg


If anyone knows a solution to this, I'm all ears (if I don't pull them off by the time I figure this out).

Tia,


Barracuda

Yes, a sample workbook please, but also update your profile. 2019 does not have the FILTER formula.