excelexcel-formuladynamic-arrays

Using dynamically created names in sumif function within a LET function


I have data that looks as follows: (obviously much larger...)

Date Customer Amount Agent
09/01/2023 David B 200 Louis
10/01/2023 Jimmy R 1500 Gene
11/01/2023 David B 350 Louis

I'm trying to make a dynamic report from that table as follows:

  • Filter the table by agent
  • Get unique names of customers
  • Total all sales by customer

(I know I can do this easily with a pivot table, but there is a reason why I want to use dynamic arrays instead)

Here is the formula that I am trying to use:

=LET(tbl, FILTER(mainTableRange,TableColumn=AgentName),
dateCol, CHOOSECOLS(tbl,1),   nameCol, CHOOSECOLS(tbl,2), amountCol, CHOOSECOLS(tbl,3),
names, UNIQUE(nameCol),
totalV,  SUMIF(nameCol,names,amountCol),
totalV)

And, eventually, I would want to end it with: HSTACK(names, totalV) (and other dynamic columns as well)

All variables return the correct information, however when I put them together in the sumif function, it returns value errors. If I use the actual ranges instead, e.g., SUMIF(Q12:Q124,names,R12:R124) then it works fine.

Why is the SUMIF function not working with the LET variables?


Solution

  • Because SUMIFS() only works with pure cell ranges, not values output by other formula. Try in this way-

    =LET(x,UNIQUE(FILTER(B2:B10,D2:D10=G2)),
    y,MAP(x,LAMBDA(t,SUMIFS(C2:C10,B2:B10,t,D2:D10,G2))),
    HSTACK(x,y))
    

    enter image description here