Adding values ignoring (-)

[NG7267]

How can I add values taking their abs value as given below.

	2
	-4
	-2
	1
	3
Total	12

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

 

[Snakehips]

NG7267,

Try the array formula.. =SUM(ABS(B1:B5))

Confirm with Ctrl + Shift + Enter

Hope that helps.

 

[NG7267]

Hi Snakehips,

Thank you very much for your reply,I have data in one sheet and summary table in another sheet, Since there are around 30 cells in the summary sheet which needs adding data (abs) values from data sheet,it is difficult to do ctrl+shift+enter every time, Is there any way that I can use first adding positives then adding negatives after multypling by (-1) in the same formula or any other solution,

Thank you so much !

 

[Snakehips]

Either way you still have to only enter a formula in each summary cell the once ???

However you can try this which although an array formula by virtue of SUMPRODUCT, does not require C + S + E confirmation.

=SUMPRODUCT((B1:B5>0)*(B1:B5)+(B1:B5<0)*(B1:B5)*(-1))

 

[FormR]

=SUMPRODUCT((B1:B5>0)*(B1:B5)+(B1:B5<0)*(B1:B5)*(-1))

Or another option:

=SUMPRODUCT(ABS(B1:B6))

 

[NG7267]

Thank you very much for your replies !

 

[Snakehips]

Or another option:

=SUMPRODUCT(ABS(B1:B6))

Not quite sure why I overcomplicated that.

 

[NG7267]

Not quite sure why I overcomplicated that.

Wow !This is fantastic ! Thank you so much ! Even though I got the answer from the previous formula, I could not figure out actually what it was !