Discussion Sum of an Array's Rows: The Formula in this Cell Contains an Error and Eternal Apostrophe's in Excel
https://techcommunity.microsoft.com/t5/excel/sum-of-an-array-s-rows-the-formula-in-this-cell-contains-an/m-p/2843449#M117858
<P class="">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 [<SPAN>=SEQUENCE(4,4,1,3)].</SPAN></P><TABLE><TBODY><TR><TD><P class="">1</P></TD><TD><P>4</P></TD><TD><P>7</P></TD><TD><P>10</P></TD></TR><TR><TD><P>13</P></TD><TD><P>16</P></TD><TD><P>19</P></TD><TD><P>22</P></TD></TR><TR><TD><P>25</P></TD><TD><P>28</P></TD><TD><P>31</P></TD><TD><P>34</P></TD></TR><TR><TD><P>37</P></TD><TD><P>40</P></TD><TD><P>43</P></TD><TD><P>46</P></TD></TR></TBODY></TABLE><P>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 [<SPAN>ROUNDDOWN(SEQUENCE(ROWS(A1#),COLUMNS(A1#),0)/COLUMNS(A1#),0)+1].</SPAN></P><TABLE><TBODY><TR><TD><P>1</P></TD><TD><P>1</P></TD><TD><P>1</P></TD><TD><P>1</P></TD></TR><TR><TD><P>2</P></TD><TD><P>2</P></TD><TD><P>2</P></TD><TD><P>2</P></TD></TR><TR><TD><P>3</P></TD><TD><P>3</P></TD><TD><P>3</P></TD><TD><P>3</P></TD></TR><TR><TD><P>4</P></TD><TD><P>4</P></TD><TD><P>4</P></TD><TD><P>4</P></TD></TR></TBODY></TABLE><P><SPAN>I'm using this array as the input for the formula </SPAN></P><P><SPAN>[=SUMIF(A6#,"="&SEQUENCE(ROWS(A1#)),A1#)] and that outputs the values I'd be looking for in their own array.</SPAN></P><TABLE><TBODY><TR><TD><P>22</P></TD></TR><TR><TD><P>70</P></TD></TR><TR><TD><P>118</P></TD></TR><TR><TD><P>166</P></TD></TR></TBODY></TABLE><P>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.</P><P><SPAN>[=SUMIF(</SPAN><STRONG><SPAN>ROUNDDOWN(SEQUENCE(ROWS(A1#),COLUMNS(A1#),0)/COLUMNS(A1#),0)+1</SPAN></STRONG><SPAN>,"="&SEQUENCE(ROWS(A1#)),A1#)]</SPAN></P><P> </P><P><SPAN>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.</SPAN></P><P><SPAN>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.</SPAN></P><P> </P><P><SPAN>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.</SPAN></P>Wed, 13 Oct 2021 20:03:12 GMTSeventhSpartan2021-10-13T20:03:12ZSum of an Array's Rows: The Formula in this Cell Contains an Error and Eternal Apostrophe's
https://techcommunity.microsoft.com/t5/excel/sum-of-an-array-s-rows-the-formula-in-this-cell-contains-an/m-p/2843449#M117858
<P class="">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 [<SPAN>=SEQUENCE(4,4,1,3)].</SPAN></P><TABLE><TBODY><TR><TD><P class="">1</P></TD><TD><P>4</P></TD><TD><P>7</P></TD><TD><P>10</P></TD></TR><TR><TD><P>13</P></TD><TD><P>16</P></TD><TD><P>19</P></TD><TD><P>22</P></TD></TR><TR><TD><P>25</P></TD><TD><P>28</P></TD><TD><P>31</P></TD><TD><P>34</P></TD></TR><TR><TD><P>37</P></TD><TD><P>40</P></TD><TD><P>43</P></TD><TD><P>46</P></TD></TR></TBODY></TABLE><P>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 [<SPAN>ROUNDDOWN(SEQUENCE(ROWS(A1#),COLUMNS(A1#),0)/COLUMNS(A1#),0)+1].</SPAN></P><TABLE><TBODY><TR><TD><P>1</P></TD><TD><P>1</P></TD><TD><P>1</P></TD><TD><P>1</P></TD></TR><TR><TD><P>2</P></TD><TD><P>2</P></TD><TD><P>2</P></TD><TD><P>2</P></TD></TR><TR><TD><P>3</P></TD><TD><P>3</P></TD><TD><P>3</P></TD><TD><P>3</P></TD></TR><TR><TD><P>4</P></TD><TD><P>4</P></TD><TD><P>4</P></TD><TD><P>4</P></TD></TR></TBODY></TABLE><P><SPAN>I'm using this array as the input for the formula </SPAN></P><P><SPAN>[=SUMIF(A6#,"="&SEQUENCE(ROWS(A1#)),A1#)] and that outputs the values I'd be looking for in their own array.</SPAN></P><TABLE><TBODY><TR><TD><P>22</P></TD></TR><TR><TD><P>70</P></TD></TR><TR><TD><P>118</P></TD></TR><TR><TD><P>166</P></TD></TR></TBODY></TABLE><P>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.</P><P><SPAN>[=SUMIF(</SPAN><STRONG><SPAN>ROUNDDOWN(SEQUENCE(ROWS(A1#),COLUMNS(A1#),0)/COLUMNS(A1#),0)+1</SPAN></STRONG><SPAN>,"="&SEQUENCE(ROWS(A1#)),A1#)]</SPAN></P><P> </P><P><SPAN>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.</SPAN></P><P><SPAN>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.</SPAN></P><P> </P><P><SPAN>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.</SPAN></P>Wed, 13 Oct 2021 20:03:12 GMThttps://techcommunity.microsoft.com/t5/excel/sum-of-an-array-s-rows-the-formula-in-this-cell-contains-an/m-p/2843449#M117858SeventhSpartan2021-10-13T20:03:12ZRe: Sum of an Array's Rows: The Formula in this Cell Contains an Error and Eternal Apostrophe's
https://techcommunity.microsoft.com/t5/excel/sum-of-an-array-s-rows-the-formula-in-this-cell-contains-an/m-p/2843823#M117875
<P><LI-USER uid="1182298"></LI-USER> </P><LI-CODE lang="applescript">=MMULT(A1#,SEQUENCE(COLUMNS(A1#))^0)</LI-CODE>Wed, 13 Oct 2021 21:39:30 GMThttps://techcommunity.microsoft.com/t5/excel/sum-of-an-array-s-rows-the-formula-in-this-cell-contains-an/m-p/2843823#M117875Detlef Lewin2021-10-13T21:39:30Z