Sum column with both - & + numbers

aapjrat5128 · Apr 8th, 2002, 07:20 PM

I have two columns with both negative and positive numbers, how can I sum both the negative and positive numbers separatly? The columns are D18:D34 and H13:H34. Thanks in advance for any help. AL

Tom Urtis · Apr 8th, 2002, 07:43 PM

I assume you'd want the negative numbers summed and displayed in one cell, and the positive (or essentially 0 and above) numbers summed and displayed in a separate cell.

So, try this in one cell:
Positive (0 and above:
=SUMIF(D18:D34,">=0")

and for summing negative numbers in another cell:
=SUMIF(D18:D34,"<0")

Is that what you want?
If so, repeat for your other range and you are good to go.

anno · Apr 8th, 2002, 07:45 PM

for negative numbers try =SUMIF(D18:D34,"<0",D18:D34)+SUMIF(H13:H34,"<0",H13:H34) and for positive numbers =SUMIF(D18:D34,">0",D18:D34)+SUMIF(H13:H34,">0",H13:H34)
by the way, i notice that the range for column d is different to column h (ie. d is row 18 to 34, while h is row 13 to 34). if this is a typo you'll have to amend the ranges in these formulas

Apr 8th, 2002, 07:43 PM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,388	I assume you'd want the negative numbers summed and displayed in one cell, and the positive (or essentially 0 and above) numbers summed and displayed in a separate cell. So, try this in one cell: Positive (0 and above: =SUMIF(D18:D34,">=0") and for summing negative numbers in another cell: =SUMIF(D18:D34,"<0") Is that what you want? If so, repeat for your other range and you are good to go. __________________ Tom Urtis Microsoft MVP - Excel

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

Apr 8th, 2002, 07:20 PM	#1
aapjrat5128 Board Regular Join Date: Apr 2002 Posts: 53	I have two columns with both negative and positive numbers, how can I sum both the negative and positive numbers separatly? The columns are D18:D34 and H13:H34. Thanks in advance for any help. AL

Apr 8th, 2002, 07:45 PM	#3
anno Board Regular Join Date: Feb 2002 Posts: 202	for negative numbers try =SUMIF(D18:D34,"<0",D18:D34)+SUMIF(H13:H34,"<0",H13:H34) and for positive numbers =SUMIF(D18:D34,">0",D18:D34)+SUMIF(H13:H34,">0",H13:H34) by the way, i notice that the range for column d is different to column h (ie. d is row 18 to 34, while h is row 13 to 34). if this is a typo you'll have to amend the ranges in these formulas