0

Here's a sample test spreadsheet - https://docs.google.com/spreadsheets/d/

What do I have to do in the ONE cell in column D such that I won't have to copy and paste the same formula in column C throughout the entire column all the time as my data grows?

2 Answers 2

1

Allen, you can also try this:

  1. Delete everything from Column D (including the header).

  2. Place the following array formula into D1:

=ArrayFormula({"Array Formula";SUMIF(ROW(B2:B),"<="&ROW(B2:B),B2:B)+B$1})

7
  • Thanks! This work great! Except it leaves hundreds of extra rows at the bottom of the column. Also, I wonder if there is a more generic way of using arrayformula if the intended operation is not a simple addition, but something more complex that still takes reference to the adjacent cell above or upwards diagonal? Commented Jan 16, 2019 at 13:44
  • 1
    You can remove superfluous row data with this addition to the formula: =ArrayFormula({"Array Formula";IF(B2:B="","",SUMIF(ROW(B2:B),"<="&ROW(B2:B),B2:B)+B$1)})
    – Erik Tyler
    Commented Jan 17, 2019 at 14:44
  • As for complex array formulas, I make a living from writing them. But there's not much I can tell you based on generalities. If you want to ask a specific question (or better yet, set up the scenario on your test sheet), I'll see what more I can offer based on that.
    – Erik Tyler
    Commented Jan 17, 2019 at 14:46
  • Thanks! I never thought that would work! I have another scenario that I'd like you to solve. It is in the 2nd sheet of the same test link. :) Commented Jan 21, 2019 at 10:57
  • 1
    Yes. In English, the SUMIF reads, "In looking at all row numbers, for any row numbers less than or equal to the current row number at any given time, sum the values in column B for those rows only" (or, shortened, sum only rows up to "this point").
    – Erik Tyler
    Commented Jan 24, 2019 at 13:33
0
  • if you want to clone column C to column D with ARRAYFOORMULA then use this:

=ARRAYFORMULA(C1:C)

  • if you want B1+B2 array:

={{"ARRAYFORMULA FOR B1+B2"};
  {MMULT(B1:B30, TRANSPOSE(ARRAYFORMULA(COLUMN(B2:B30)^0)))}}

  • if you want B2+C2 array:

={{"ARRAYFORMULA FOR B2+C2"};
  {MMULT(B2:C30, TRANSPOSE(ARRAYFORMULA(COLUMN(B2:C30)^0)))}}

4

3
  • Hmmm, this does not give the same results as the normal formula column, and I have no idea how those functions are working together with MMULT? Commented Jan 16, 2019 at 13:33
  • @AllenSierra what is your question?
    – user0
    Commented Jan 16, 2019 at 13:36
  • Uh, well, I'm looking for an arrayformula based formula that gives the same results as the column C in my spreadsheet. :) Commented Jan 21, 2019 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.