Sum of Negative or Positive, Exclude 0 or Include 0

[ibmy]

Hi Excel Experts ?

Sample of 20k row data I face weekday :

dx 2.1.21.xlsb
	B	C	D	E	F
1		Sum of Negative		Sum of Positive
2	Value	Exclude 0	Include 0	Exclude 0	Include 0
3	0.1			0.1
4	0
5	0
6	0.1			0.1	0.2
7	-0.4	-0.4	-0.4
8	1			1	1
9	-0.6
10	-0.2	-0.8	-0.8
11	0.6			0.6	0.6
12	0
13	-0.1	-0.1
14	0
15	-0.3	-0.3
16	0
17	-0.3
18	-0.1
19	-0.1
20	-0.2
21	-0.6	-1.3	-1.7
22	0.1
23	0.3
24	0.4
25	0.4
26	0.1			1.3	1.3
27	-0.1	-0.1	-0.1
28	1			1	1
Sheet7

Found similiar thread ( Ignoring Blank cells find Sum, count no. Of negative numbers ) but it for Negative numbers, blank cell and not continous sum for each row.

Rule for Sum of Negative ( Exclude 0 ) :
1. Start with Negative value.
2. Stop sum if value are Positive or 0. ( >=0 )
3. Start new find for Negative value on next row.

Rule for Sum of Negative ( Include 0 ) :
1. Start with Negative value.
2. Stop sum if value is Positive. ( >0 )
3. Start new find for Negative value on next row.

Rule for Sum Postive (Exclude 0,<=0 & Include 0,<0 ) same as above, just opposite of it.

Thanks.

 

[maabadi]

Try this with one difference at result (yellow cell):

Book1
	A	B	C	D	E	F
1		Sum of Negative		Sum of Positive
2	Value	Exclude 0	Include 0	Exclude 0	Include 0
3	0.1			0.1
4	0
5	0
6	0.1			0.1	0.2
7	-0.4	-0.4	-0.4
8	1			1	1
9	-0.6
10	-0.2	-0.8	-0.8
11	0.6			0.6
12	0
13	-0.1	-0.1
14	0
15	-0.3	-0.3
16	0
17	-0.3
18	-0.1
19	-0.1
20	-0.2
21	-0.6	-1.3	-1.7
22	0.1
23	0.3
24	0.4
25	0.4
26	0.1			1.3	1.9
27	-0.1	-0.1	-0.1
28	1	0	0	1	1
29
Sheet1

Cell Formulas
Range	Formula
B3:B28	B3	=IF(($A4>=0)*($A3<0)+(A4=""),SUMIFS($A$2:$A3,$A$2:$A3,"<"&0)-SUM(B$2:B2),"")
C3:C28	C3	=IF(($A4>0)*($A3<0)+(A4=""),SUMIFS($A$2:$A3,$A$2:$A3,"<"&0)-SUM(C$2:C2),"")
D3:D28	D3	=IF(($A4<=0)*($A3>0)+(A4=""),SUMIFS($A$2:$A3,$A$2:$A3,">"&0)-SUM(D$2:D2),"")
E3:E28	E3	=IF(($A4<0)*($A3>0)+(A4=""),SUMIFS($A$2:$A3,$A$2:$A3,">="&0)-SUM(E$2:E2),"")

 

[Peter_SSs]

I have a different slight difference.

(BTW @maabadi you also have a difference in B28 & C28)

21 02 03.xlsm
	A	B	C	D	E
1		Sum of Negative		Sum of Positive
2	Value	Exclude 0	Include 0	Exclude 0	Include 0
3	0.1			0.1
4	0
5	0
6	0.1			0.1	0.2
7	-0.4	-0.4	-0.4
8	1			1	1
9	-0.6
10	-0.2	-0.8	-0.8
11	0.6			0.6
12	0				0.6
13	-0.1	-0.1
14	0
15	-0.3	-0.3
16	0
17	-0.3
18	-0.1
19	-0.1
20	-0.2
21	-0.6	-1.3	-1.7
22	0.1
23	0.3
24	0.4
25	0.4
26	0.1			1.3	1.3
27	-0.1	-0.1	-0.1
28	1			1	1
Sheet2 (2)

Cell Formulas
Range	Formula
B3:B28	B3	=IF(AND(A3<0,A4>=0),SUMIF(A$3:A3,"<0")-SUM(B$2:B2),"")
C3:C28	C3	=IF(AND(A3<0,OR(A4>0,A4="")),SUMIF(A$3:A3,"<0")-SUM(C$2:C2),"")
D3:D28	D3	=IF(AND(A3>0,A4<=0),SUMIF(A$3:A3,">0")-SUM(D$2:D2),"")
E3:E28	E3	=IF(AND(A3>=0,OR(A4<0,A4="")),IF(SUMIF(A$3:A3,">0")-SUM(E$2:E2)=0,"",SUMIF(A$3:A3,">0")-SUM(E$2:E2)),"")

 

[maabadi]

@Peter_SSs I Know. I add it because show Complete Result for Last Row.

 

[Akuini]

With vba:

Sub a1160492a()
'https://www.mrexcel.com/board/threads/sum-of-negative-or-positive-exclude-0-or-include-0.1160492/
Dim va
Dim a As Double, b As Double, c As Double, d As Double, x As Double
Dim i As Long

va = Range("A3", Cells(Rows.Count, "A").End(xlUp))
ReDim vb(1 To UBound(va, 1), 1 To 4)

For i = 1 To UBound(va, 1)
    x = va(i, 1)
    If x < 0 Then
        a = a + x
        If i = UBound(vb, 1) Then vb(i, 1) = a
    Else
        If a <> 0 Then vb(i - 1, 1) = a: a = 0
    End If
    
    If x < 0 Then
          b = b + x
          If i = UBound(vb, 1) Then vb(i, 2) = b
    ElseIf x > 0 Then
        If b <> 0 Then vb(i - 1, 2) = b: b = 0
    End If
    
    If x > 0 Then
        c = c + x
        If i = UBound(vb, 1) Then vb(i, 3) = c
    Else
        If c <> 0 Then vb(i - 1, 3) = c: c = 0
    End If
    
    If x > 0 Then
          d = d + x
          If i = UBound(vb, 1) Then vb(i, 4) = d
    ElseIf x < 0 Then
        If d <> 0 Then vb(i - 1, 4) = d: d = 0
    End If

Next

Range("B3").Resize(UBound(vb, 1), 4) = vb
End Sub

Book2
	A	B	C	D	E
1
2	Value
3	0.1			0.1
4	0
5	0
6	0.1			0.1	0.2
7	-0.4	-0.4	-0.4
8	1			1	1
9	-0.6
10	-0.2	-0.8	-0.8
11	0.6			0.6
12	0				0.6
13	-0.1	-0.1
14	0
15	-0.3	-0.3
16	0
17	-0.3
18	-0.1
19	-0.1
20	-0.2
21	-0.6	-1.3	-1.7
22	0.1
23	0.3
24	0.4
25	0.4
26	0.1			1.3	1.3
27	-0.1	-0.1	-0.1
28	1			1	1
Sheet3

 

[ibmy]

Thanks to all kind-hearted helper ? @maabadi @Peter_SSs @Akuini . I wish you all good health ? , the days ahead are filled with joy and prosperity ?

@Akuini Thanks for the code. May I request for another 2 different ouput, the rule are still the same. I tried to modify the code you given but not success since I no good at code.

Output 1:

2.15.2021.xlsb
	D	E	F	G	H	I
1		Sum of Positive			Sum of Negative
2	Set 1	Exclude 0	Include 0	Set 2	Exclude 0	Include 0
3	0.1	0.1		-0.3	-0.3
4	0			0
5	0			0		-0.3
6	0.1	0.1	0.2	0.1
7	-0.4			0
8	1	1	1	-0.4	-0.4
9	-0.6			0
10	-0.2			-0.3	-0.3
11	0.6	0.6		0		-0.7
12	0		0.6	0.5
13	-0.1			0.2
14	0			0.1
15	-0.3			-0.1	-0.1
16	0			0		-0.1
17	-0.3			0.7
18	-0.1			-0.4
19	-0.1			-0.8
20	-0.2			-1.2
21	-0.6			-1.7	-4.1
22	0.1			0		-4.1
23	0.3			0.2
24	0.4			-0.6	-0.6	-0.6
25	0.4			0.2
26	0.1	1.3	1.3	-0.2	-0.2
27	-0.1			0
28	1	1	1	-1.7	-1.7	-1.9
Sheet4

What difference from before:
(1) Set of data : Sum of Positive & Negative has their own data set (Column D = Positive , Column G = Negative )
Before : Both Sum Positive & Negative are using same data set in Column A.

(2) Arrangement of data : Sum of Positive is calculated first then Negative (Column E & F = Sum of Positive, Column H & I = Sum of Negative)
Before : Sum of Negative is calculated first in Column B & C, then Positive in Column D & E.

(3) Star and End of Column : The data start in Column D, ends in Column I.
Before : Start in Column A, ends in Column E


Output 2:

2.15.2021.xlsb
	D	E	F	G
1	Sum of Positive		Sum of Negative
2	Set 1	Include 0	Set 2	Include 0
3	0.1		-0.3
4	0		0
5	0		0	-0.3
6	0.1	0.2	0.1
7	-0.4		0
8	1	1	-0.4
9	-0.6		0
10	-0.2		-0.3
11	0.6		0	-0.7
12	0	0.6	0.5
13	-0.1		0.2
14	0		0.1
15	-0.3		-0.1
16	0		0	-0.1
17	-0.3		0.7
18	-0.1		-0.4
19	-0.1		-0.8
20	-0.2		-1.2
21	-0.6		-1.7
22	0.1		0	-4.1
23	0.3		0.2
24	0.4		-0.6	-0.6
25	0.4		0.2
26	0.1	1.3	-0.2
27	-0.1		0
28	1	1	-1.7	-1.9
Sheet7

Same as Output 1, just remove "Exclude 0" part for both Sum Positive & Negative in Column E & H so:
(1) Set of data for Negative is moved from Column G to F
(2) Column "Include 0" for Positive is move from Column F to E & Column "Include 0" for Negative is move from Column I to G
(3) Now, Last Column is G, before is Column I. Start of column is still the same for both Output which is D