Formula Sum Positives If not start sum Negative

[motilulla]

Using Excel 2010

Hello,

I got results in the columns “G”, in the column “E” there is formula which drive result from Column “G”

In the column “C” I have set formula which sum if value in the column “G” is >””

But I am not able to set the formula if column =”” get negative values sum as shown in the Column “D”…so far need formula for column D as shown in the example below.

MrExcel Question.xls
	A	B	C	D	E	F	G	H
1
2
3
4			With MY	Need This	Result BY		Results
5			Formula	Result	Formula		Results
6			1	1	1		1
7			2	2	2		2
8			3	3	3		3
9				-1
10				-2
11				-3
12				-4
13			1	1	1		1
14			2	2	2		2
15			3	3	3		3
16				-1
17				-2
18				-3
19				-4
20				-5
21				-6
22			1	1	1		1
23			2	2	2		2
24			3	3	3		3
25				-1
26				-2
27				-3
28				-4
29				-5
30				-6
31				-7
32				-8
33				-9
34				-10
35			1	1	1		1
36
37
38
Sheet3

Cell Formulas
Range	Formula
E6:E35,C6:C35	C6	=IF(E6="","",E6)

Please help

Thank you all.

Regards,
Moti

 

[Peter_SSs]

Like this?

22 12 26.xlsm
	D	E	F	G
4	Need This	Result BY		Results
5	Result	Formula		Results
6	1	1		1
7	2	2		2
8	3	3		3
9	-1
10	-2
11	-3
12	-4
13	1	1		1
14	2	2		2
15	3	3		3
16	-1
17	-2
18	-3
19	-4
20	-5
21	-6
22	1	1		1
23	2	2		2
24	3	3		3
25	-1
26	-2
27	-3
28	-4
29	-5
30	-6
31	-7
32	-8
33	-9
34	-10
35	1	1		1
Sheet2 (2)

Cell Formulas
Range	Formula
D6:D35	D6	=IF(G6="",IF(G5="",D5-1,-1),G6)
E6:E35	E6	=IF(G6="","",G6)

 

[motilulla]

Like this?

22 12 26.xlsm
	D	E	F	G
4	Need This	Result BY		Results
5	Result	Formula		Results
6	1	1		1
7	2	2		2
8	3	3		3
9	-1
10	-2
11	-3
12	-4
13	1	1		1
14	2	2		2
15	3	3		3
16	-1
17	-2
18	-3
19	-4
20	-5
21	-6
22	1	1		1
23	2	2		2
24	3	3		3
25	-1
26	-2
27	-3
28	-4
29	-5
30	-6
31	-7
32	-8
33	-9
34	-10
35	1	1		1
Sheet2 (2)

Cell Formulas
Range	Formula
D6:D35	D6	=IF(G6="",IF(G5="",D5-1,-1),G6)
E6:E35	E6	=IF(G6="","",G6)

Peter, formula worked as treat, thank you for your kind help

Good Luck!

Kind Regards,
Moti

 

[motilulla]

Peter, formula worked as treat, thank you for your kind help

Good Luck!

Kind Regards,
Moti

Hello again, I need a modification could it be possible to get formula results in the column “C” even the numbers are as shown in the column “E” where are =“” count in the column “C” as – and add them -1,-2, and -3….otherwise + as 1,2,3 soon

MrExcel Question.xls
	A	B	C	D	E	F
1
2
3
4			Need This		Results
5			Result		Results
6			1		1
7			2		2
8			3		3
9			-1
10			-2
11			-3
12			-4
13			1		4
14			2		7
15			3		12
16			4		15
17			-1
18			-2
19			-3
20			-4
21			-5
22			1		20
23			2		24
24			3		26
25			4		27
26			5		32
27			6		49
28			-1
29			-2
30			-3
31			-4
32			-5
33			-6
34			-7
35			1		28
36			2		50
37
38
Sheet4

Please help

Thank you all.

Kind Regards,
Moti

 

[Sufiyan97]

Try

Book1
	D	E	F	G	H
6
7		Need This		Results
8		Result		Results
9		1		1
10		2		2
11		3		3
12		-1
13		-2
14		-3
15		-4
16		1		4
17		2		7
18		3		12
19		4		15
20		-1
21		-2
22		-3
23		-4
24		-5
25		1		20
26		2		24
27		3		26
28		4		27
29		5		32
30		6		49
31		-1
32		-2
33		-3
34		-4
35		-5
36		-6
37		-7
38		1		28
39		2		50
40
Sheet2

Cell Formulas
Range	Formula
E9:E39	E9	=IF(G9="",IF(G8="",N(E8)-1,-1),IF(AND(G9<>"",G8<>""),N(E8)+1,1))

 

[motilulla]

Try

Book1
	D	E	F	G	H
6
7		Need This		Results
8		Result		Results
9		1		1
10		2		2
11		3		3
12		-1
13		-2
14		-3
15		-4
16		1		4
17		2		7
18		3		12
19		4		15
20		-1
21		-2
22		-3
23		-4
24		-5
25		1		20
26		2		24
27		3		26
28		4		27
29		5		32
30		6		49
31		-1
32		-2
33		-3
34		-4
35		-5
36		-6
37		-7
38		1		28
39		2		50
40
Sheet2

Cell Formulas
Range	Formula
E9:E39	E9	=IF(G9="",IF(G8="",N(E8)-1,-1),IF(AND(G9<>"",G8<>""),N(E8)+1,1))

Sufiyan97, Greta formula did worked as request perfect!

Thank you so much for your kind help

Good Luck!

Kind Regards,
Moti

 

[Sufiyan97]

You're welcome.
Glad it helped.

 

[Peter_SSs]

Sufiyan97, Greta formula did worked as request perfect!

You could also consider this simpler one.

22 12 26.xlsm
	E	F	G
7	Need This		Results
8	Result		Results
9	1		1
10	2		2
11	3		3
12	-1
13	-2
14	-3
15	-4
16	1		4
17	2		7
18	3		12
19	4		15
20	-1
21	-2
22	-3
23	-4
24	-5
25	1		20
26	2		24
27	3		26
28	4		27
29	5		32
30	6		49
31	-1
32	-2
33	-3
34	-4
35	-5
36	-6
37	-7
38	1		28
39	2		50
Sheet3

Cell Formulas
Range	Formula
E9:E39	E9	=IF(G9="",IF(G8="",E8-1,-1),IF(N(G8),E8+1,1))

 

[motilulla]

You could also consider this simpler one.

22 12 26.xlsm
	E	F	G
7	Need This		Results
8	Result		Results
9	1		1
10	2		2
11	3		3
12	-1
13	-2
14	-3
15	-4
16	1		4
17	2		7
18	3		12
19	4		15
20	-1
21	-2
22	-3
23	-4
24	-5
25	1		20
26	2		24
27	3		26
28	4		27
29	5		32
30	6		49
31	-1
32	-2
33	-3
34	-4
35	-5
36	-6
37	-7
38	1		28
39	2		50
Sheet3

Cell Formulas
Range	Formula
E9:E39	E9	=IF(G9="",IF(G8="",E8-1,-1),IF(N(G8),E8+1,1))

Peter, yes this is good one, thank you so much for you kind help

Good Luck.

Kind Regards,
Moti