# VBA - Total Number Positive Or Negative, Exclude 0 or Include 0

#### ibmy

##### Board Regular
Hi Excel Experts,

I been using the formula but for 90k-110k row it took 9 minutes for 1 column. The time takes longer if I open many programs ( tabs Chrome, Spotify,etc..) My laptop's fan is spining fast and louder when apply the formula. For 20k row is ok , less than 10 seconds.

The formula I use currently is
1) =if(A3>0,1,"") , =if(A3>=0,1,"") , =if(A3<0,1,""), =if(A3<=0,1,"")
2) Then apply =IF(B3="","",IF(SUM(\$B\$3:B3)=SUM(\$B\$3:B4),SUM(\$B\$3:B3)-SUM(\$C\$2:C2),""))

My sample of 90k-120k weekday data:

dx 2.13.21.xlsx
DEFGHIJK
1Set 1 (+)Exclude 0Include 0Set 2 (-)Exclude 0Include 0
2
30-0.1
40-0.2
50.31-0.433
601
70.3-0.1
80.323-0.22
9-10
10-0.10
110-0.3
12-0.5-0.52
13-0.204
14-0.10.8
150.31-0.3
1601-0.52
17-0.70
180.4-0.3
190.8-0.5
201.2-0.3
211.74-0.54
22040
23-0.2-0.317
240.6110.1
Sheet2

Rule for Total Number of Negative ( Exclude 0 ) :
2. Stop count if value are Positive or 0 ( >=0 )
3. Start new find for Negative value on next row.

Rule for Total Number of Negative ( Include 0 ) :
2. Stop count 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.

Column A,B,C,G,H have other data.

Similiar to this thread is Sum of Negative or Positive, Exclude 0 or Include 0 , but it for sum and only 1 set data.

If there any formula that simplier or use less resources is also welcomed.

Thanks.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### GlennUK

##### Well-known Member
It's not clear what you are doing. You don't say what cells those formulas are in, and the formulas refer to columns A,B,&C, but they aren't in your sheet capture shown. That said, you are using SUM to current row for column B and C ... wouldn't it be more efficient to have a couple of simple running total columns so that massive ranges are NOT referenced?

#### ibmy

##### Board Regular
I put other data in Column A,B,C,G,H such time,sum,etc...so that vba provided here is not use that column.

The formula cell I currently use is, I put in another Sheet before I put the result in Column E,F,J,K in Sheet 1. Example, I open Sheet 2, to find Total Number Positive Exclude 0. Firstly, in Column A I put the data, Column B (=if(A3>0,1,""), then Column C (=IF(B3="","",IF(SUM(\$B\$3:B3)=SUM(\$B\$3:B4),SUM(\$B\$3:B3)-SUM(\$C\$2:C2),""))) ). The result I got in Column C in Sheet 2, i put in Sheet 1 Column E, Then continute opening new sheet for remaining =if(A3>=0,1,"") , =if(A3<0,1,""), =if(A3<=0,1,"").

p/s: Set 1(+) is Total Number Positive , Set 2(-) is Total Number Negative

Last edited:

#### GlennUK

##### Well-known Member
You are being very vague and confusing ... you showed the formula as was used elsewhere, before you moved it to different columns, and then showed the layout after the move? That explains why your formulas don't match the sheet snip. But then you just repeat the formulas that showed before, and have not commented on my suggestion of using extra columns for running totals to prevent having to access massive ranges in formulas. I said that to reduce your calculation time ... which is what you requested in the first place.

Replies
5
Views
350
Replies
14
Views
374
Replies
2
Views
117
Replies
4
Views
232
Replies
14
Views
655

1,129,587
Messages
5,637,244
Members
416,963
Latest member
samfuge

### 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.

### Which adblocker are you using?

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

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