Using dynamically created names in sumif function within a LET function

Question

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?

Answer 1

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