Search code examples
excelfrequency-distribution

counting numbers in corresponding range

let suppose that we have following numbers

49  57  38  73  81
74  59  76  65  69
54  56  69  68  78
65  85  49  69  61
48  81  68  37  43
78  82  43  64  67
52  56  81  77  79
85  40  85  59  80
60  71  57  61  69
61  83  90  87  74

and let us suppose we have following class intervals

Lower limit  upper limit
35              41
42              48
49              55
56              62
63              69
70              76
77              83
84              90

what i want is to count how many numbers occurs between given intervals , for instance between [35-41], in excel i have tried following range

=COUNTIF($A$1:$E$10,"<="&J3)-COUNTIF($A$1:$E$10,"<"&I3)

where j3 and I3 are upper and low limit, i think this formula is correct, for instance let us consider following numbers

1    2   3  4  5  6   7  8  9  10  11  12

we would like to know how many elements are in range of 7-12, there are twelve number that are less or equal to 12, and 6 number that are less then 7, so difference between is 6 number, so we have

7 8 9 10 11 12

so i think my formula is correct right?

Solution

  • With data like:

    enter image description here

    In I2 enter:

    =COUNTIF(A$1:E$10,"<=" & H2)

    and in I3 enter:

    =COUNTIF(A$1:E$10,"<=" & H3)-SUM($I$2:I2)

    enter image description here

    This takes advantage of the fact that your thresholds do not overlap and have no gaps.