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

Scott Craner · Accepted Answer · 2021-04-20 20:03:48Z

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))

edited Apr 20, 2021 at 20:03

answered Apr 20, 2021 at 19:57

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

laurent Over a year ago

Thanks Scott, this is working just fine! I am now struggling to do the same with TEXTJOIN instead of SUM (the goal would be to merge names of all customers who ordered something over a given year). Do you have any clue on how to achieve that?

Scott Craner Over a year ago

That is a new question. If this worked for this question please mark as correct by clicking the check mark by the answer. New questions get new posts

laurent Over a year ago

Alright. Being new on this site, I need to learn the customs :-)

Collectives™ on Stack Overflow

Call spill range within dynamic array formula

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related