excelexcel-formula
Is it possible to create a dymamic array which sums different columns depending on row?
I know how to use BYROW(), CHOOSECOLS() and LAMBDA() etc. to sum up columns.
e.g.
=BYROW(CHOOSECOLS(B2:M3,1,2,3,4,5,6),LAMBDA(a,SUM(a)))
I would like to sum up columns but the columns to be summed up would be different in each row depending on a a cell in that row
e.g. in row 1, I want to sum up columns 1,2,3,4 but in row 2 I want to sum up 5,6,7,8,9,10,11 and 12
I was thinking something along the lines of:
=BYROW(b2:m3,LAMBDA(a,SUM(CHOOSECOLS(a,TEXTSPLIT(A2,",")))))
where A2 would have 1,2,3,4 and A3 would have 5,6,7,8,9,10,11,12
However this does not work
Is this possible?
Solution
Pretty simple use the whole range in BYROW() function, TAKE() the first column while for sum DROP() the first col:
=BYROW(A2:M3,LAMBDA(α,SUM(CHOOSECOLS(DROP(α,,1),--TEXTSPLIT(TAKE(α,,1),",")))))
- Copy paste cells from button row
- Excel: Dynamic stacking or arrays n-number of times
- Get the value of the second non-empty cell in a row
- SUMIF with UNIQUE criteria for multiple columns, adding all values with same name
- How can I use filter function in Excel to filter based on selections including those that are "ALL"?
- Convert Excel formula to Power Query (if countifs)
- Openpyxl - I can't set cell format and sum values correctly in time format (h:mm)
- Find/Replace Row number within Formula
- Extracting & summing data from a single cell in excel
- How to map table references in a JSON file to corresponding values in an Excel file using Python?
- Convert number in excel to M or B
- How to have instructional text in Excel?
- How to copy a range to several worksheets by using a list of worksheets to copy to
- How to count resolved comments only via VBA in Excel
- Excel VBA - how do I get the RowSource of a ComboBox to change depending on the value of another Combobox?
- check if a file is open in Python
- How to copy only plain text of cells in Excel?
- VBA - Capitalise range of cells before the last period
- Getting the next value of the same user if the type is "no type"
- Reporting Services export to Excel causes 'Repair' and removes styles
- Excel VBA macro to group rows based on condition
- Convert table to reflect column names as row values
- Combined two arrays and the result returned has an empty key and value
- VBA: ListBox Change event firing twice
- Excel VBA offset without activating the cell
- Creating a dymamic array which sums different columns depending on row
- Excel's fullname property with OneDrive
- Why I can't compare two dates using comparison symbols in Excel?
- How to I call/trigger a ribbon control sub from a regular sub?
- If certain columns are empty within a range, delete entire row (Excel macro)