Counting inbetween numbers

[Stephen_IV]

I have 1 more question. I have been trying to count between certain numbers. For instance if I had in column A

5
12
2
1
7
8
14
19
11
22
6

I would like a formula to count say between 2 and 19 which would give me 4. Can anyone help. By the way there will not be any duplicate numbers. Thanks in advance Stephen.

 

[Aladin Akyurek]

=SUMPRODUCT(--($A$2:$A$12 > E2),--($A$2:$A$12 < F2))

where E2 houses a lower limit like 2 and F2 an upper limit like 19.

 

[Brian from Maui]

I have 1 more question. I have been trying to count between certain numbers. For instance if I had in column A

5
12
2
1
7
8
14
19
11
22
6

I would like a formula to count say between 2 and 19 which would give me 4. Can anyone help. By the way there will not be any duplicate numbers. Thanks in advance Stephen.

4?

Aladin,

Would Countif be as efficient as Sumproduct?

 

[Stephen_IV]

Aladin,

That gives me a count of 6. I am looking for a count of 4 the numbers inbetween 2 and 19 but not counting the 2 and 19.

 

[Brian from Maui]

Aladin,

That gives me a count of 6. I am looking for a count of 4 the numbers inbetween 2 and 19 but not counting the 2 and 19.

Should be more like 7

 

[Aladin Akyurek]

Aladin,

That gives me a count of 6. I am looking for a count of 4 the numbers inbetween 2 and 19 but not counting the 2 and 19.

Check your data!

 

[tusharm]

Suppose your data are in A2:A12. Suppose the first number you want to look up is in B2 and the 2nd is in B3. Then, the formula =ABS(MATCH(B2,A2:A12,0)-MATCH(B3,A2:A12,0))-1 will give you the number of elements *by position* between the two numbers.

This will only work if the numbers in the list are unique. Otherwise, it will always use the first occurence of the number(s).

I have 1 more question. I have been trying to count between certain numbers. For instance if I had in column A

5
12
2
1
7
8
14
19
11
22
6

I would like a formula to count say between 2 and 19 which would give me 4. Can anyone help. By the way there will not be any duplicate numbers. Thanks in advance Stephen.

 

[Stephen_IV]

Aladin the numbers I am looking to count between are

5
12
2 <----------
1
7
8
14
19 <----------
11
22
6
Which will give me 4

 

[Aladin Akyurek]

...
Would Countif be as efficient as Sumproduct?

all.xls
	A	B	C	D	E	F
1		WorkBookSummary
2		CalcTime(Millisec)		%	Book	MicroSecs
3		ReCalc	FullCalc	Volatile	OvHead	/Formula
4	SumP	0.2	12.2	1.6%	7.3	12,238.2
5	CountIf	0.2	8.7	2.3%	6.8	8,663.9
6
FastXL

One drawback with CountIf is that it cannot distinguish between text-formatted numbers and real numbers. Otherwise, apart from a small difference in volatility, it's slightly faster for the between counts.

 

[Aladin Akyurek]

Aladin the numbers I am looking to count between are

5
12
2 <----------
1
7
8
14
19 <----------
11
22
6
Which will give me 4

I see. Since it's already answered...