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

#### ACCA369

##### New Member
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

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

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

#### Attachments

• Screenshot_20210127-144709_Office.jpg
221.3 KB · Views: 8

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### alz

##### Board Regular
Hi try this!

TempO.xlsx
ABCDE
1Data
210
320
40*Sum Number after last positive
625
70* Ignoring Blank Cells
8Count Number of negatvie Value after last positive Number
10-25
11-40* Ignoring Blank Cells
120Count Number of Zero Value after last positive Number
14-15
15
16-50
17-40
180
19
200
21-50
Sheet15
Cell Formulas
RangeFormula
D5D5=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))
D9D9=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))
D13D13=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.

##### Well-known Member
Column A is Helper Column.
Try this:
Book1
ABCDEF
1No.Data
2110Last Positive Number100
3220Sum After Last Positive Number-155
430Last Positive Row Number12
54-15Count Negative values after4
6525Count Zero values after2
760
87
98-50
109-25
1110-40
12110
1312100
1413-15
1514
1615-50
1716-40
18170
1918
20190
2120-50
22
Sheet1
Cell Formulas
RangeFormula
E2E2=LOOKUP(2,1/(\$B\$2:\$B\$21>0),\$B\$2:\$B\$21)
E3E3=SUM(OFFSET(\$B\$2:\$B\$21,E4,0,ROWS(\$B\$2:\$B\$21)-E4,1))
E4E4=LOOKUP(2,1/(\$B\$2:\$B\$21>0),\$A\$2:\$A\$21)
E5E5=COUNTIFS(OFFSET(\$B\$2:\$B\$21,E4,0,ROWS(\$B\$2:\$B\$21)-E4,1),"<" & 0)
E6E6=COUNTIFS(OFFSET(\$B\$2:\$B\$21,E4,0,ROWS(\$B\$2:\$B\$21)-E4,1), 0)

#### Peter_SSs

##### MrExcel MVP, Moderator
Another version with relatively short formulas and avoiding the volatile function OFFSET

21 01 27.xlsm
ABCD
1Data
210First row after last positive14
320Sum Numbers after last positive-155
40Count Number of negatvie Value after last positive Number4
5-15Count Number of Zero Value after last positive Number2
625
70
8
9-50
10-25
11-40
120
13100
14-15
15
16-50
17-40
180
19
200
21-50
After last positive
Cell Formulas
RangeFormula
D2D2=AGGREGATE(14,6,ROW(A2:A100)/(A2:A100>0),1)+1
D3D3=SUM(INDEX(A:A,D2):A100)
D4D4=COUNTIF(INDEX(A:A,D2):A100,"<0")
D5D5=COUNTIF(INDEX(A:A,D2):A100,0)

Replies
9
Views
195
Replies
5
Views
292
Replies
1
Views
82
Replies
14
Views
344
Replies
11
Views
428

1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back