Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question
    
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
add comment

1 Answer

up vote 1 down vote accepted

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:

enter image description here

Then, in another work sheet I have:

enter image description here

share|improve this answer
    
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
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.