Get Sum of Positive & Negative Numbers Separately.

[Prem]

Hi,

For Example, I have a list of numbers like this

3
-4
6
5
-2

Now, I need to get the sum of all positive numbers & sum of all negative numbers in different cells. Is it Possible to get using Formulas.

The Order (& also the number) of the Positive & Negative Numbers varies.

Thanks,

Prem.

[User3]

For sum of positive numbers

=SUMIF(A1:A100,">0")

For sum of negative numbers

=SUMIF(A1:A100,"<0")

[Prem]

Thank You Very Much Sir.

Can this be further simplified - Like, I need to Just select ONCE to get the positive numbers sum & the corresponding Negative numbers Sum gets updated without me having to select again.

Thanks.

[starguy]

Thank You Very Much Sir.

Can this be further simplified - Like, I need to Just select ONCE to get the positive numbers sum & the corresponding Negative numbers Sum gets updated without me having to select again.

Thanks.

Hi
Can you explain what do you mean by selecting once?

[Prem]

Hi,

For example, the datas are in column 1. What i do is Paste the above the codes in the last rows of column 2 & 3 (positive sum in cloumn 2 & Negative sum in column 3). Then, when in the column 2 cell, I change the codes like
=SUMIF(A2:A6,">0"). Then I do the same, when in column 3 (for negative sum).

The same process I have to repeat for over 100 groups of data. So I will have to change the values 100*2 = 200 times if i were to change for both positive & negative sums. If Negative sum formula is related to positive sum formula, then i need to do ONLY 100 times.

[starguy]

Hi,

For example, the datas are in column 1. What i do is Paste the above the codes in the last rows of column 2 & 3 (positive sum in cloumn 2 & Negative sum in column 3). Then, when in the column 2 cell, I change the codes like
=SUMIF(A2:A6,">0"). Then I do the same, when in column 3 (for negative sum).

The same process I have to repeat for over 100 groups of data. So I will have to change the values 100*2 = 200 times if i were to change for both positive & negative sums. If Negative sum formula is related to positive sum formula, then i need to do ONLY 100 times.

Can you attach the sample file in .zip format for better understanding of your requirement and that formula could be implemented there.

[Prem]

I am attaching the sample file.

[starguy]

I am attaching the sample file.

Follow the following steps

1) Just apply formula for Positive Sum for all groups. You should use absolute range reference, i-e your formula should be like this
=SUMIF($A$2:$A$6,">0")

2) Copy all cells of Positive Sum column from below the heading row to the end of data and paste in next column for Negative Sum.

3) Keep the cells selected in Negative Sum column and press Ctrl+H and adjust the settings as below in Find and Replace window.

Find what: ">0"
Replace with: "<0"

Look in: Formulas
(keep other options same)

and press Repalce All button.

Note: If after pressing Replace All button it gives error message of not finding any data, dont worry, what you have to do is, select any cell with Positive Sum formula and press F2 and select ">0") and paste in the Find what and Replace with box and change the sign > to < in Replace with box then press Replace All button.

[Prem]

Sir, Thank You for Providing the solution.

But, I don't know how to do the 1st Step. (As I am a n00b in Excel)

1) Just apply formula for Positive Sum for all groups. You should use absolute range reference, i-e your formula should be like this
=SUMIF($A$2:$A$6,">0")

So, Can U Plz. do it in my sample file & attach it, so i can learn where to paste the formula (for my positive sum column).

Thanks.

[starguy]

Sir, Thank You for Providing the solution.

But, I don't know how to do the 1st Step. (As I am a n00b in Excel)


So, Can U Plz. do it in my sample file & attach it, so i can learn where to paste the formula (for my positive sum column).

Thanks.

See the attached file.

[Prem]

Thank You Sir, Got it.

[starguy]

Thank You Sir, Got it.

You're welcome.

[aganesan99]

Hi,

Suppose if I have a data in two columns.
a 1
a -2
a 3
a1 4
a1 -5
a1 6
I need to separate the positive and negative values in both a and a1. what formula should I use?

Its urgent can someone help on this..

Thanks

[jubiesxl]

Please delete..I had too many windows open and got cross linked. Sorry

Hi,

For example, the datas are in column 1. What i do is Paste the above the codes in the last rows of column 2 & 3 (positive sum in cloumn 2 & Negative sum in column 3). Then, when in the column 2 cell, I change the codes like
=SUMIF(A2:A6,">0"). Then I do the same, when in column 3 (for negative sum).

What you could do is set up the formula once with "absolute" references

for example for the positives =sumif($A$2:$A$6,">0") and then copy it anywhere in the spreadsheet, this formula now will always reference to your first column

same for negatives =sumif($A$2:$A$6,"<0")

[aganesan99]

Thanks a lot