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))))))
- Excel VBA - Skip sub or end sub IF Statement
- What's the difference between "Unpivot Columns" and "Unpivot Only Selected Columns" in Power Query?
- Find all matches that use the 4 digits in sheet1 to find the 10 digit phone numbers by matching the last 4 numbers in sheet2
- Excel VBA- Loop Through Target Cells in a Table for Tracking Changes
- VBA Concatenate 2 columns without loop
- Excel: What should iferror return in order to avoid #value! error
- Excel VLOOKUP for 3 columns
- Excel: How to check if a value that meets the specified criteria exists in a lookup table?
- Showing like number format of two numbers separated by dashes in Excel
- Excel distinct count with conditions
- Different background colors for even and odd rows
- excel adding 1 on all alphanumeric string
- For an Excel Formula's variable taking a Range, input row number by the result of a formula
- how to change the text number automatically in excel without retype the formula?
- Excel: Find last value in an array
- Combining formulas
- How to skip calculating a cell if its left cell has an error value
- Power Query - splitting column
- Getting "At least one sheet must be visible" when trying to open excel spreadsheet. What to do?
- Update cells in a range based on cells in a range of a different dimensions, using VBA
- Timer on user form in Excel VBA
- Generating CSV file for Excel, how to have a newline inside a value
- Circular Reference in Dynamic Array Formula
- Excel array formula that references previously calculated values within the array
- Detect bold texts in a cell and add ";" before those texts
- Convert an array of bytes into a string?
- Deleting rows from Excel sheet based on the values in column A and Column H
- Dir Function to find file
- How to add corresponding values to a different sheet with duplicate values (while keeping the duplicates intact)?
- Find row when Cumulative sum reaches certain value with condition