Loop SUMPRODUCT or LAMBDA in Range
I am trying to use the same formula repeatedly in a range. Doing it with VBA is easy, but I am trying to do it without using VBA and all in one formula. I am trying to use SEQUENCE and OFFSET but am always getting a spill alert. What am I doing wrong here? The goal is to multiply the value in the first column with a fixed variable dependent on what is chosen in the last column, i.e. CAT2 or 3 and then sum it all up WITHOUT any intermediate columns.
=SUM(LAMBDA(Nh;Cu;Nh*Cu)(OFFSET(CHOOSE(MATCH(OFFSET(K$18;z;0);$F$7:$H$7;0);$F$9;$G$9;$H$9);V43;0);OFFSET(E$18;z;0)))
z=SEQUENCE(ROWS(B18:B34);1;E18;1)
Solution
This is a spillable version of your SUMPRODUCT()
=LET(dat,B1:F17,lkp,K3:M6,MAKEARRAY(ROWS(lkp),COLUMNS(dat),LAMBDA(r,c,SUMPRODUCT(INDEX(dat,,c),INDEX(lkp,r,MATCH(H1:H17,K1:M1))))))
- Is it possible to read the value of the Office Theme option that's currently applied in the Microsoft Excel settings and use it in VBA?
- Lookup multiple criteria
- Return all column headers when there are multiple yes entries from a vlookup
- VBA FindFirst if there is no match
- VBA set a dynamic range based on “unique” IDs in a column
- Character encoding in Excel spreadsheet and what Java charset to use to decode it
- Disable warning about Excel text cell containing numeric value in OfficeOpenXml
- Excel LAMBDA + spill
- Using Excel Lambda function to count numbers that meet a condition (in conjunction with groupby)
- How do I filter results from a list based on previously-filtered results?
- Sort rows based on multiple criteria
- Convert Range to a table
- Problems with Excel handling of the month of March in German language
- Excel IF multiple columns
- Increase row height of Excel cell (merged cell & text wrapped), if cell has multiple lines
- Copy Formula To Multiple Cells Using EPPlus
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- Using .NET how would you set a CheckBox to true within an array?
- Save copied workbook in specified location
- Aligning Bars between labels in Bar charts
- How to get 2D combination of words?
- Using Solidworks VBA code, how do I create a reference plane at a specified angle from the Top Plane?
- Bug with For Each enumeration on x64 Custom Classes
- Putting many python pandas dataframes to one excel worksheet
- Sum rows containing only the first appearance of value in separate column
- C# Excel OutOfMemory exception caused by inserting to a range
- VBA Excel "Compile error: duplicate declaration" only on certain machines in shared workbook
- How to suppress Update Links warning?
- Attempting to create a pivot-like report using a formula
- Excel SUM function is not working (shows 0), but using Addition (+) works