Sum over a formula in Google Sheets

Question

I have a Google Sheet and I need to add a lot of stuff like

(ABS(A1)+A1)/A1+ (ABS(A2)+A2)/A2+...+ (ABS(A10)+A10)/A10

but I don't want to write out the sum each time for each term A1,A2,A3,..., A10

Is there a way to write the sum more concise? like

SUM( (|x| + x)/x : for x from A1 to A10)

I know that's not correct but that is what I want.

Have you tried grabbing the square corner of the cell containing the formula and dragging it down the column (for example). If certain cell refs are fixed, use the $ sign to make the cell reference absolute (such as $A$1) otherwise the cell ref will increment as you drag it down. — graham, Jan 8, 2021 at 19:58

Marios · Accepted Answer · 2021-01-11 13:32:59Z

1

You can use an Arrayformula to achieve your goal.

You don't need to drag down a formula nor specify cells. This will work for every correct calculation in column A:

=Sum(Arrayformula(Iferror((Abs(A2:A8)+A2:A8)/A2:A8,"")))

or ignore iferror if you are sure A2:A8 contains numbers (and not text or blank elements):

=Sum(Arrayformula((Abs(A2:A8)+A2:A8)/A2:A8))

answered Jan 8, 2021 at 23:19

Marios

3852 silver badges9 bronze badges

I'm sorry, could you explain a little bit what each of the items mean? For example, I do not want to sum over the entirety of column A, suppose I only want to sum from A2 to A8 specifically
– Christopher Rose
Jan 11, 2021 at 13:25
Abs(A1:A)+A1:A)/A1:A is your current solution. The difference is that instead of applying it to just a single element you can apply it to a range of elements A1:A. To apply it to a range of elements you need to use arrayformula. You don't want to apply it for every cell in column A but only for the ones which have a value (non blank value). To do that you can use iferror to prevent empty cells from being used by the formula. @ChristopherRose
– Marios
Jan 11, 2021 at 13:27
I'm sorry, but I do not understand. For example, the solution I want should not be a matrix, but should be a single number. Also, for the A column you wrote in your example, the sum I want should be 3 but your sum has 2 three times (or is it 0?) -- I don't understand
– Christopher Rose
Jan 11, 2021 at 13:30
@ChristopherRose I updated my answer. Check it again.
– Marios
Jan 11, 2021 at 13:33

Add a comment |

1 Answer 1