# Counting inbetween numbers

#### Stephen_IV

##### Well-known Member
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.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
=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.

Stephen_IV said:
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?

Would Countif be as efficient as Sumproduct?

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.

Stephen_IV said:

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

Stephen_IV said:

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.

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

Stephen_IV said:
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 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

Brian from Maui said:
...
Would Countif be as efficient as Sumproduct?
all.xls
ABCDEF
1WorkBookSummary
2CalcTime(Millisec)%BookMicroSecs
4SumP0.212.21.6%7.312,238.2
5CountIf0.28.72.3%6.88,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.

Stephen_IV said:
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

Replies
5
Views
166
Replies
2
Views
112
Replies
2
Views
408
Replies
4
Views
166
Replies
9
Views
285

1,203,245
Messages
6,054,368
Members
444,720
Latest member
saathvik

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