sum at the end of positive and negative number

Excel777

Well-known Member
Joined
Jul 3, 2009
Messages
886
hi all
i have column a have positive and negative numbers i want to add sum in column b at each sequence of positive and negative number for example first positive sequence start from 1 to 2 so sum equal 3 second negative number start at 3- to -5 so results equal -11

i want formula to add in column b to solve this problem

1
2 Result 3
-3
-3
-5 Result -11
6
2
4
4 Result 16
-1
-5
-6 Result -12
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,616
Office Version
  1. 365
Platform
  1. Windows
Try this in B2 and fill down

=if(SIGN(A2)=SIGN(A3),"",SUM(A$1:A1)-SUM(B$1:B1))
 

Excel777

Well-known Member
Joined
Jul 3, 2009
Messages
886
it's not working it give me these results
Results
1
21
-3
-3
-5-4
6
2
4
47
-1
-5
-6-2
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,616
Office Version
  1. 365
Platform
  1. Windows
When you post examples that people can not copy and paste into excel to test formulas, that the formulas are not usually tested and incorrect results are inevitable.

=if(SIGN(A2)=SIGN(A3),"",SUM(A$2:A2)-SUM(B$1:B1))

It is preferable to minimise potential errors and confusion by posting examples with XL2BB rather than using screen captures, direct copy and paste or typed examples with no formatting.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,887
Messages
5,574,839
Members
412,620
Latest member
sharma7s
Top