Google-sheets – Apply a formula to each cell in column before computing a sum of the cells

formulasgoogle sheetsgoogle-sheets-arrayformula

How could I apply a formula to each cell in a column before computing a sum of the cells in Google spreadsheet?

In other words, I have a column of cells with numbers. And what I want is to iterate over each row in the column (i.e. each cell), apply a formula to the number in the cell and get a result of the formula. Then I want to sum all of those results.

And all of this I would like to perform in one cell (i.e. in one query).

Best Answer

=SUM(ARRAYFORMULA((B2:B26-$B$1)^2))/A1

=SUM(ARRAYFORMULA(A1:A4*(SUM(2, 3))))

Related Solutions

Google-sheets – Return value in column A in preceding cells in column B using an array formula

Assuming the data starts in row 1, try in B1:

=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),{{0;FILTER(ROW(A:A),LEN(C:C)=0)},{FILTER(A:A,LEN(C:C)=0);""}},2),))

Out of interest, the formula simplifies a bit if you are retrieving from cells below:

=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},LEN(C:C)=0),2),))

Google-sheets – Applying a Formula to All Cells in a Column (Even New Cells)

Try:

=ArrayFormula(if(len(H2:H)*len(G2:G), iferror(H2:H/G2:G, 0),))

The formula will output results if there are entries in col H AND col G. See if that works for you ?

Best Answer

Related Solutions

Google-sheets – Return value in column A in preceding cells in column B using an array formula

Google-sheets – Applying a Formula to All Cells in a Column (Even New Cells)

Related Topic