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

ACCA369

New Member
Joined
Dec 27, 2020
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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
 

Attachments

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

Some videos you may like

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
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
Hi try this!

TempO.xlsx
ABCDE
1Data
210
320
40*Sum Number after last positive
5-15Answer-155
625
70* Ignoring Blank Cells
8Count Number of negatvie Value after last positive Number
9-50Answer4
10-25
11-40* Ignoring Blank Cells
120Count Number of Zero Value after last positive Number
13100Answer2
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.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,606
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,393
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top