-1

I have this sheet:

https://docs.google.com/spreadsheets/d/10YVUO2a-XftaCOX73tOiOwQfu0t80iKPJjQrRDiht1A/edit?usp=sharing

In the column "E" I need the same operation as ARRAYFORMULA in column C and D.

In column E I need to make a simple SUM and this is what does not work.

How do I resolve?

UPDATED:

Can I use this magic formula:

=ARRAYFORMULA(IF(A1:A;A1:A*(2*B1:B);""))

but with SUM?

I need to use this magic formula which repeat for every rows the formula i choose, but I need repeat in each rows a formula with SUM that now doesn't work. How to?

  • While it's great to offer an example spreadsheet to show what you're trying to do, your question really should have enough information to stand alone. When that link no longer works (after you've decided that you no longer need the file) this question will be of no use to anyone else. – ale Aug 11 '14 at 23:02
  • @VidarS.Ramdal, neither link can solve my problem. I'm wrong? You may try in my sheet linked? – ginolin Aug 12 '14 at 1:49
  • Come on, can be so difficult? – ginolin Aug 12 '14 at 15:59
  • Dear peanuts, sorry if I have not explained well. Now I will try to do it properly. I need in column "E" to have the same automatic formula I get with the formula that I have written in column "D". Only in column "E" I need a SUM function does not work. Clear now? Open the link and see by yourself. – ginolin Aug 12 '14 at 17:17
1

I believe the question is: how can I provide a product of the sums of columns A and B, from the current row down to the end of the sheet, and apply this automatically down the column with an array formula?

=ArrayFormula(SUMIF(ROW(A:A);">="&ROW(A:A);A:A)*SUMIF(ROW(B:B);">="&ROW(B:B);B:B))

As per OP request, this will populate a header, and mask any output on rows where there is nothing in column A or B:

=ArrayFormula(IF(LEN(A:A)*LEN(B:B),IF(ROW(A:A)=1,"Counter",SUMIF(ROW(A:A);">="&ROW(A:A);A:A)*SUMIF(ROW(B:B);">="&ROW(B:B);B:B)),))

  • Wow. Fantastic. Great. For all that the "geniuses" told me over here before you I was feeling very stupid. Thanks a lot, real genius. – ginolin Aug 14 '14 at 0:20
  • Just one more thing: If I want to put a title in the first row of the column? – ginolin Aug 14 '14 at 0:38
  • For the title: If I add this: =ArrayFormula(IF(ROW(A:A)=1; "Counter"; ArrayFormula(SUMIF(ROW(A:A);">="&ROW(A:A);A:A)*SUMIF(ROW(B:B);">="&ROW(B:B);B:B))) the title works, but the column continue with error to the bottom of the file and is not what I need. The error is: "Error: The arguments of the matrix for IF are of a different size." How to? – ginolin Aug 14 '14 at 1:43
  • I can't really see an issue with your formula (the second ArrayFormula is unnecessary, but shouldn't affect it). Can you double check this is the exact formula you have used (copy and paste it directly from the sheet, perhaps). – AdamL Aug 14 '14 at 1:54
  • Et voilà! This is the exact formula from my spreadsheet: =ArrayFormula(IF(ROW(A:A)=1; "Counter"; ARRAYFORMULA(SUMIF(ROW(Games!A:A);">="&ROW(Games!F:F);Games!F:F)/SUMIF(ROW(Games!A:A);">="&ROW(Games!C:C);Games!C:C)*100))). Everything works (also if I add a row from the middle), but after last cell that has content the other cells are full of error: "Error: The arguments of the matrix for IF are of a different size." How to? – ginolin Aug 14 '14 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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