Ignoring Blank cells find Sum, count no. Of negative numbers

[ACCA369]

Data
10
20
0
-15
25
0

-50
-25
-40
0
100
-15

-50
-40
0

0 *** Ignoring Blank cells
-50
* sum numbers after last positive number
answer : -155


** ignoring blank cells
count number of Negative values after last positive number
answer: 4

***Ignoring Blank cells
count number of zero values after last positive number
answer : 2

 

[alz]

Hi try this!

TempO.xlsx
	A	B	C	D	E
1	Data
2	10
3	20
4	0		*Sum Number after last positive
5	-15		Answer	-155
6	25
7	0		* Ignoring Blank Cells
8			Count Number of negatvie Value after last positive Number
9	-50		Answer	4
10	-25
11	-40		* Ignoring Blank Cells
12	0		Count Number of Zero Value after last positive Number
13	100		Answer	2
14	-15
15
16	-50
17	-40
18	0
19
20	0
21	-50
Sheet15

Cell Formulas
Range	Formula
D5	D5	=SUM(IF((ROW($A$2:$A$21)-ROW($A$2)+1)>AGGREGATE(14,6,($A$2:$A$21>0)*(ROW($A$2:$A$21)-ROW($A$2)+1),1),$A$2:$A$21))
D9	D9	=SUMPRODUCT(((ROW($A$2:$A$21)-ROW($A$2)+1)>AGGREGATE(14,6,($A$2:$A$21>0)*(ROW($A$2:$A$21)-ROW($A$2)+1),1))*($A$2:$A$21<0))
D13	D13	=SUMPRODUCT(((ROW($A$2:$A$21)-ROW($A$2)+1)>AGGREGATE(14,6,($A$2:$A$21>0)*(ROW($A$2:$A$21)-ROW($A$2)+1),1))*EXACT($A$2:$A$21,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[maabadi]

Column A is Helper Column.
Try this:

Book1
	A	B	C	D	E	F
1	No.	Data
2	1	10		Last Positive Number	100
3	2	20		Sum After Last Positive Number	-155
4	3	0		Last Positive Row Number	12
5	4	-15		Count Negative values after	4
6	5	25		Count Zero values after	2
7	6	0
8	7
9	8	-50
10	9	-25
11	10	-40
12	11	0
13	12	100
14	13	-15
15	14
16	15	-50
17	16	-40
18	17	0
19	18
20	19	0
21	20	-50
22
Sheet1

Cell Formulas
Range	Formula
E2	E2	=LOOKUP(2,1/($B$2:$B$21>0),$B$2:$B$21)
E3	E3	=SUM(OFFSET($B$2:$B$21,E4,0,ROWS($B$2:$B$21)-E4,1))
E4	E4	=LOOKUP(2,1/($B$2:$B$21>0),$A$2:$A$21)
E5	E5	=COUNTIFS(OFFSET($B$2:$B$21,E4,0,ROWS($B$2:$B$21)-E4,1),"<" & 0)
E6	E6	=COUNTIFS(OFFSET($B$2:$B$21,E4,0,ROWS($B$2:$B$21)-E4,1), 0)

 

[Peter_SSs]

Another version with relatively short formulas and avoiding the volatile function OFFSET

21 01 27.xlsm
	A	B	C	D
1	Data
2	10		First row after last positive	14
3	20		Sum Numbers after last positive	-155
4	0		Count Number of negatvie Value after last positive Number	4
5	-15		Count Number of Zero Value after last positive Number	2
6	25
7	0
8
9	-50
10	-25
11	-40
12	0
13	100
14	-15
15
16	-50
17	-40
18	0
19
20	0
21	-50
After last positive

Cell Formulas
Range	Formula
D2	D2	=AGGREGATE(14,6,ROW(A2:A100)/(A2:A100>0),1)+1
D3	D3	=SUM(INDEX(A:A,D2):A100)
D4	D4	=COUNTIF(INDEX(A:A,D2):A100,"<0")
D5	D5	=COUNTIF(INDEX(A:A,D2):A100,0)