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

[ibmy]

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
	D	E	F	G	H	I	J	K
1	Set 1 (+)	Exclude 0	Include 0			Set 2 (-)	Exclude 0	Include 0
2
3	0					-0.1
4	0					-0.2
5	0.3	1				-0.4	3	3
6	0					1
7	0.3					-0.1
8	0.3	2	3			-0.2	2
9	-1					0
10	-0.1					0
11	0					-0.3
12	-0.5					-0.5	2
13	-0.2					0		4
14	-0.1					0.8
15	0.3	1				-0.3
16	0		1			-0.5	2
17	-0.7					0
18	0.4					-0.3
19	0.8					-0.5
20	1.2					-0.3
21	1.7	4				-0.5	4
22	0		4			0
23	-0.2					-0.3	1	7
24	0.6	1	1			0.1
Sheet2

Rule for Total Number of Negative ( Exclude 0 ) :
1. Start with Negative value.
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 ) :
1. Start with Negative value.
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.

 

[GlennUK]

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]

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

 

[GlennUK]

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.