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

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?


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


    enter image description here