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