Sum unique negative values and positive values

[gokzee]

Hello!!

i am looking a formula to sum unique neg and positive values in a column separately to two cells

column A

-100
-100
-100
#n/a
#n/a
#n/a
120
120
120
120
-190
-190
200
200

cell 1= -100+-190
cell2 = 120+200

is there a formula to achieve this ..i tried with sumif but dint get the required output

thanks n advance

Gokz

[TheExcelphile]

Hi Gokz,

I've used a helper column to solve your problem.

You'll need to put an error validation in your original data, though, because having #N/A in your data is never a good idea, and any formula you use won't work as long as you have those #N/As.

Don't forget to click the little star at the bottom left of this post if you feel I helped!

[icestationzbra]

if your data are in A1:A14, try the following ARRAY formulae.

for negative numbers:

Please Login or Register  to view this content.

or Excel 2007

Please Login or Register  to view this content.

for positive numbers:

Please Login or Register  to view this content.

or Excel 2007

Please Login or Register  to view this content.