Share via

The Formula in this Cell Contains an Error and Eternal Apostrophe's: Sumif on an array

Anonymous
2021-10-13T18:49:13+00:00

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.

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

3 answers

Sort by: Most helpful
  1. Anonymous
    2021-10-28T00:41:12+00:00

    Excel 365 Pro Plus with Power Pivot and Power Query.

    Reconfigured like a business app with labeled row and column Table.

    No formulas, no VBA macro, no array generators.

    PivotChart added.

    https://www.mediafire.com/file/rm54avrmz2kq7t8/10_27_21b.xlsx/file https://www.mediafire.com/file/g1j1x14f5ymzle1/10_27_21b.pdf/file

    Was this answer helpful?

    0 comments No comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more

  3. Anonymous
    2021-10-22T15:04:09+00:00

    Try this, copied down:

    =SUM(INDEX(SEQUENCE(4,4,1,3),ROW(A1)))

    Was this answer helpful?

    0 comments No comments