Call spill range within dynamic array formula

Question

I am trying to query a list of sales stored in Excel using a dynamic array formula referencing a spill range and this does not work.

Here is the context:

• I have a table tabSales containing the sales, with the following data : date, customer, amount, year (calculated using =YEAR([Date])
• I want to display on another sheet the amount sold year by year for a given customer to be selected by the user in cell B1 (named SelCust)

Here is what I have tried on the report sheet:

• I have used the UNIQUE and FILTER functions to make the report display on column D the years where the selected customer has actually bought something : =UNIQUE(FILTER(tabSales[Year],tabSales[Customer]=SelCust)) (formula input in cell D2)
• I have then tried to use the SUM and FILTER functions and Excel's spill range feature to calculate for each year the total amount sold to the selected customer : =SUM(FILTER(tabSales[Amount],(tabSales[Year]=D2#)*(tabSales[Customer]=SelCust))) (formula input in E2)

Unfortunately, this last formula unfortunately does not work:

• Excel returns #N/A in E2
• Cell E2 does not spill over E3, E4, ...

The lack of spilling makes me wonder whether the spill range reference within the FILTER function is recognized...

How could I get this formula to work ?

Sales table Non-working formula

Answer 1

Use SUMIFS():

=SUMIFS(tabSales[Amount],tabSales[Year],D2#,tabSales[Customer],SelCust)

This should return an array properly.

If one wants to do both columns in one dynamic formula we can use LET and CHOOSE:

=let(
    slcust,tabSales[Customer],
    amt,tabSales[Amount],
    yr,tabSales[Year],
    unYr,UNIQUE(FILTER(yr,slcust=SelCust)),
    sm,SUMIFS(amt,yr,unyr,slcust,SelCust),
    Choose({1,2},unyr,sm))