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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,613
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,613
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,861
Messages
5,574,708
Members
412,613
Latest member
EFRATA
Top