Q
Q
Qwixo2017-11-08 13:54:09
Google Sheets
Qwixo, 2017-11-08 13:54:09

For Google Spreadsheets there is a formula with Arrayformula. How does it work?

Good afternoon!
On the stackoverflow.com service, he asked a question about how to solve his problem. They answered me and gave me a formula, it works. But I can't understand why it works :) Explain please.
The task is as follows:
Column A contains different numbers. In column B, you need to display the sum of all previous rows of column A using arrayformula. But you need to do this with arrayformula, so as not to stretch the usual formula down if necessary. Like this:
5a02e0d8aa881611856579.jpeg
Using standard formulas, this is done simply:
B1: = A1
B2: = A2 + $B$1
B3: = A3 + $B$2
B4: = A4 + $B$3
and drag down.
The working formula from stackoverflow.com looks like this:
=ARRAYFORMULA(SUMIF(ROW(A1:A10), "<="&ROW(A1:A10), A1:A10))
Please describe how it works. I am familiar with ARRAYFORMULA, SUMIF, STRING and "&", but I don't understand how this bundle works in this case. Perhaps I do not quite understand how ARRAYFORMULA works.

Answer the question

In order to leave comments, you need to log in

3 answer(s)
D
developer, 2018-09-23
@developer

ARRAYFORMULA works sequentially with each row in the range.
In each line, she does what is written, but it is written to calculate the sum of the previous ones, at the output you have the final amount.

F
Fi3xeR, 2019-03-22
@Fi3xeR

Absolutely incomprehensible explanation. If SUMIF in conjunction with ARRAYFORMULA behaves exactly the same as without it, why then when writing the same formula line by line in the form =SUMIF(ROW(A1), "<="&ROW(A1), A1:A10) , =SUMIF(ROW(A2), "<="&ROW(A2), A1:A10), =SUMIF(ROW(A3), "<="&ROW(A3), A1:A10), etc., the result is completely inadequate???
It is completely incomprehensible, due to what "it is written to calculate the sum of the previous ones"? Where is that written? I only see "less than or equal to" and not "calculate sum of previous". And if you replace "less than or equal to" with, for example, "greater than or equal to", the formula also counts something, but it's completely incomprehensible what.
In this situation, "SUMMESLI"

G
Grigory Boev, 2019-10-28
@ProgrammerForever


=СУММЕСЛИ(СТРОКА(A1); "<="&СТРОКА(A1); A1:A10)
=СУММЕСЛИ(СТРОКА(A2); "<="&СТРОКА(A2); A1:A10)
=СУММЕСЛИ(СТРОКА(A3); "<="&СТРОКА(A3); A1:A10)

etc., the result is completely inadequate???
Here, most likely, it unfolds like this:
=SUMIF(ROW(A1); "<="&ROW(A1:A10); A1:A10) => the sum of all {A1:A10} in the lines of which {1} is less than or equal to {ROW( A1:A10)}
=SUMIF(ROW(A2), "<="&ROW(A1:A10), A1:A10) => sum of all {A1:A10} in rows where {2} are less than or equal to { 1, 2 , 3, 4, 5, 6, 7, 8, 9, 10}
=SUMIF(ROW(A3), "<="&ROW(A1:A10), A1:A10) => sum of all { 10, 20, 30 , 40 ...} in strings where {3} are less than or equal to { 1, 2, 3 , 4, 5, 6, 7, 8, 9, 10}
...

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question