Adding up Absolute numbers

nycnights · May 3rd, 2002, 11:46 AM

I'm working on a spreadsheet in which one column has both negative and positive number. Below the column I would like a sum of the absolute values of those figures I tried this formula but it didn't work. =sum(abs(A1:a5)) I also tried =sum(abs(a1):abs(a5)). Any ideas.. Please help.

-Danny

Aladin Akyurek · May 3rd, 2002, 11:49 AM

=SUMPRODUCT((ABS(A1:A5)))

would do.

nycnights · May 3rd, 2002, 11:55 AM

Thanks Aladdin. It works perfectly.

-Danny

IML · May 3rd, 2002, 12:00 PM

I'm glad you got a workable solution. Another way to do this without an array type solution would be
=SUMIF(A1:A5,">0")-SUMIF(A1:A5,"<0")

I'd only go with this if you are using the formula often in a big sheet and notice performance issues.

Good luck

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 3rd, 2002, 11:46 AM	#1
nycnights New Member Join Date: May 2002 Location: Queens, New York Posts: 3	I'm working on a spreadsheet in which one column has both negative and positive number. Below the column I would like a sum of the absolute values of those figures I tried this formula but it didn't work. =sum(abs(A1:a5)) I also tried =sum(abs(a1):abs(a5)). Any ideas.. Please help. -Danny

May 3rd, 2002, 11:49 AM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	=SUMPRODUCT((ABS(A1:A5))) would do.

May 3rd, 2002, 11:55 AM	#3
nycnights New Member Join Date: May 2002 Location: Queens, New York Posts: 3	Thanks Aladdin. It works perfectly. -Danny

May 3rd, 2002, 12:00 PM	#4
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	I'm glad you got a workable solution. Another way to do this without an array type solution would be =SUMIF(A1:A5,">0")-SUMIF(A1:A5,"<0") I'd only go with this if you are using the formula often in a big sheet and notice performance issues. Good luck