I need an Excel formula which allows summing a single column. If the summing value comes negative, it will show “1” and if the value comes negative it will show the true value.
Just for an example: C1:C20 the summing value is 15, it will show “1” or C1:C20 the summing value is -15; it will show “-15”.
Please Help.
It's actually very simple.
=IF(SUM(D3:D12)>0,1,SUM(D3:D12))
Just to explain the what the formula does…
Hope this helps.
According to your requirement what you want is if the total value (Sum) is positive the answer should show “1” and if the total value (sum) is negative the answer should show the same negative total value.
I will explain a way to get the result that you expected by using the same example that you took.(That means I will take the sum of C1 to C 20)
First select a cell that you want to get the expected result. (Please don’t take C1, C20 and any cell between them)
Then type the following formula on that cell.
=IF (SUM (C1:C20)>0,"1", SUM (C1:C20))
In the following image I have typed that formula and see the result “1” when the total (sum) value is positive.
In the following image it will show the same negative result when the total value is negative.
As such you are able to get the result which you want.
