Max Veteran Posted January 22, 2015 Veteran Share Posted January 22, 2015 I have a column of numbers which include negative figures. I wish to separate the positive and negative figures, then add them all together. For example: The column has this: 10 20 -30 40 -20 I want all the negative numbers adding up, and all the positive numbers adding up, resulting in (for the above example): -50 and 70. There must be an easy way to do this, but I'm not an Excel whizz! The actual speadsheet that I need to do this on is here in my Dropbox: https://www.dropbox.com/s/gcej7hudhxh5bja/figures.csv?dl=0 Thanks! Link to comment Share on other sites More sharing options...
0 zhangm Supervisor Posted January 22, 2015 Supervisor Share Posted January 22, 2015 You can apply a filter to the column of values to show only values greater than zero, copy those to a new column, and do the same for values less than zero; then sum them using =sum(cells). You can also just use =if(A1>0,A1), and =if(A1<0,A1) to output values of positive and negative numbers from your data column into new columns, then sum the output. Probably more clever ways to do it, but I'm also not an Excel whiz. figures.zip Link to comment Share on other sites More sharing options...
0 Max Veteran Posted January 22, 2015 Author Veteran Share Posted January 22, 2015 Brilliant! Thank you very much! Link to comment Share on other sites More sharing options...
0 ShawnDude Posted January 22, 2015 Share Posted January 22, 2015 The simple solution is to use =sumif(range of numbers,"<0") and = sumif(range of numbers,">0") to get the sums of values less than and greater than 0. Link to comment Share on other sites More sharing options...
Question
Max Veteran
I have a column of numbers which include negative figures.
I wish to separate the positive and negative figures, then add them all together.
For example: The column has this:
10
20
-30
40
-20
I want all the negative numbers adding up, and all the positive numbers adding up, resulting in (for the above example): -50 and 70.
There must be an easy way to do this, but I'm not an Excel whizz!
The actual speadsheet that I need to do this on is here in my Dropbox: https://www.dropbox.com/s/gcej7hudhxh5bja/figures.csv?dl=0
Thanks!
Link to comment
Share on other sites
3 answers to this question
Recommended Posts