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

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel