Get Sum of Positive & Negative Numbers Separately.

Originally Posted by 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.

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

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.

Originally Posted by 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.

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

Originally Posted by Prem

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.

Originally Posted by 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)


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.

Originally Posted by Prem

Thank You Sir, Got it.

You're welcome.

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

Originally Posted by 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).

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")