# calc: finding the number of numerical values in a given interval

I want to build a histogram of a set of values. So I need to get the number of values that are in a considered interval.

Say my values are on a sheet named 'LIST' in column A. On current sheet, I have in column 1 and 2 the lower and upper bound of the interval.

With `=COUNTIF(LIST!\$A:\$A,">="&A1)` I am able to get the number of values that are higher then the value in A1.

But how do I get the number of values that are both lower than A2 and higher than A1. The following: `=COUNTIF(LIST!\$A:\$A,AND(">="&A1,"<"&B1))` throws me an error.

Update:

I have in a seperate worksheet (called List) the following values in Column A:

ColumnA

1
2
3
10
11
12
10
11
12

Then I want in my sheet to show (column A and B are values which have been entered manually. I'm looking for a formula for column C which will display results like:

``````ColA  ColB    ColC
0      5       3
5      10      0
10     15      6
15     20      0
``````
-
No, what I need is the number of values in the interval. I'll edit the question to make it clearer. – kebs Dec 21 '12 at 11:02
Ok for the edits. Nope, I do have 6 values in the interval [10:15[ – kebs Dec 21 '12 at 13:00

## 1 Answer

You need this:

=COUNTIF(List!\$A:\$A,">="&A1) - COUNTIF(List!\$A:\$A,">="&B1)

So, I have re-created your question with screen shots. This is my worksheet called List:

Then, in another work sheet I have:

-
Err... Either you misunderstood my question, either I completely missed something. In your example, I see 10 values '2'. So first line is correct, you have indeed 10 values in the interval [1:11[, but second line is not, you have 10 values in the interval [1:5[, not 4... And third and fourth line should be 0, not "". And there should not be in the formula any reference to a specific cell of 'list', as in the second term of your 'if' formula. – kebs Dec 21 '12 at 13:28
Okay, I see. In your example, you have 10 values. They are all the same (2). So you do agree that you have 10 values in your list that are lying in the range [0:10], don't you ?. But these 10 values are also lying in the range [1:5], as 2 is between 1 and 5. And if I ask for the numbers of values in the range [5:6] then I have none, so it should give 0. This is the point of the 'COUNTIF' function, to count the numbers of cells that do respect some constraint. But I'm looking for is a way to "mix" two constraints, i.e. instead of giving only a constraint such as "higher than xx", I want – kebs Dec 21 '12 at 13:52
[continued] "Higher than xx" AND "lower than yy". – kebs Dec 21 '12 at 13:52
Thanks for your efforts, but no, it does not work (try with my original 9-values example, for instance). For me, it gives me the 4 values '10 5 0 Err:518'. I don't really get this last error, as all the terms of the formula are correct, I'll investigate this. – kebs Dec 21 '12 at 14:37
Oh, I got the error, it was just because of missing value in the third part of the IF function. But still does not work. – kebs Dec 21 '12 at 14:52