Count specific value in range

[ibmy]

Hi Mr Excel,

How to get result in Column Q & S?
Given number of range to check for Column Q in M , Column S in O
The Range to check in Column G.

Example : Cell O6 show 2 number of range , then check how many value < -0.6 in Column G and the result is 1 in Column S.
Same goes to Column M for Column Q

Sample from 300k row

sample.xlsb
	G						M		O		Q		S
1	B						Count -		Count +		< -0.6		> 0.6
2
3	-0.1						1
4	0
5	0.3
6	0.6								2				1
7	-0.5
8	-1.1
9	-0.9						3				2
10	0.4
11	0.2
12	0.2
13	0.3
14	0.8								5				1
15	-0.7						1				1
16	0
17	1						1						1
Sheet1

 

[ibmy]

edit : O6 is 2 and the range number are 0.3 & 0.6 , the result should be blank cell because > 0.6 should be start the count at 0.7

sorry for typo.

 

[Habtest]

To clarify:
Why is S6 "1", should the condition in fact be >=0.6?
Why is S17 "1", I thought Q17 should be "1" and S17 blank, for:

Given number of range to check for Column Q in M , Column S in O

 

[ibmy]

To clarify:
Why is S6 "1", should the condition in fact be >=0.6?
Why is S17 "1", I thought Q17 should be "1" and S17 blank, for:

Sorry, I entry wrong data

Yes you are right :

1) S6 should be blank cell
2) O17 should be 1, not M17 so S17 is 1

I update the data :

sample.xlsb
	G						M		O		Q		S
1	B						Count -		Count +		< -0.6		> 0.6
2
3	-0.1						1
4	0
5	0.3
6	0.6								2
7	-0.5
8	-1.1
9	-0.9						3				2
10	0.4
11	0.2
12	0.2
13	0.3
14	0.8								5				1
15	-0.7						1				1
16	0
17	1								1				1
Sheet1

 

[Peter_SSs]

Is this what you are after?

23 05 06.xlsm
	G						M	N	O	P	Q	R	S
1	B						Count -		Count +		< -0.6		> 0.6
2
3	-0.1						1				0
4	0
5	0.3
6	0.6								2				0
7	-0.5
8	-1.1
9	-0.9						3				2
10	0.4
11	0.2
12	0.2
13	0.3
14	0.8								5				1
15	-0.7						1				1
16	0
17	1								1				1
Count-+

Cell Formulas
Range	Formula
Q3:Q17	Q3	=IF(M3="","",COUNTIF(INDEX(G:G,ROW()-M3+1):G3,"<-0.6"))
S3:S17	S3	=IF(O3="","",COUNTIF(INDEX(G:G,ROW()-O3+1):G3,">0.6"))

 

[ibmy]

Thanks @Peter_SSs

The formula works fast on 300k row data

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.