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