Oct 13 2021 01:03 PM
I'm attempting to create an array that automatically sums the rows of another array. For the sake of having an example here's an array to sum the rows of and I'll say it starts in A1 [=SEQUENCE(4,4,1,3)].
1 | 4 | 7 | 10 |
13 | 16 | 19 | 22 |
25 | 28 | 31 | 34 |
37 | 40 | 43 | 46 |
The current working solution I have is to make a new array that corresponds to the row of each value and for the example I'll say it starts in A6 [ROUNDDOWN(SEQUENCE(ROWS(A1#),COLUMNS(A1#),0)/COLUMNS(A1#),0)+1].
1 | 1 | 1 | 1 |
2 | 2 | 2 | 2 |
3 | 3 | 3 | 3 |
4 | 4 | 4 | 4 |
I'm using this array as the input for the formula
[=SUMIF(A6#,"="&SEQUENCE(ROWS(A1#)),A1#)] and that outputs the values I'd be looking for in their own array.
22 |
70 |
118 |
166 |
So technically I'm able to achieve what I want, but if possible I'd like to condense the formulas. When I've done this in the past for different formulas I've just been able to copy the formula from A6 into another formula wherever "A6#" exists.
[=SUMIF(ROUNDDOWN(SEQUENCE(ROWS(A1#),COLUMNS(A1#),0)/COLUMNS(A1#),0)+1,"="&SEQUENCE(ROWS(A1#)),A1#)]
In the spreadsheet I'm working on I've done this just off to the side to figure out how to automatically sum the rows of an array and when I try to condense the formulas it tells me "The Formula in this Cell Contains an Error" the red dashed cell outline error.
I then tried it in a new spreadsheet and when I paste the formula in it automatically adds an apostrophe in front of it so it won't run the formula. No amount of deleting the apostrophe prevents it from reappearing when hitting enter, so now I'm just confused.
I know I can just be fine with having the separate array exist for the sake of not having any error, but I'm just confused as to why it isn't working. Is there a way to prevent this from happening and allow the formulas to consolidate? Is there a better way to automatically sum the individual rows of an array? Any help or explanation would be greatly appreciated.
Oct 13 2021 02:39 PM
Solution=MMULT(A1#,SEQUENCE(COLUMNS(A1#))^0)
Oct 13 2021 02:39 PM
Solution