count occurances of range of numbers in unknown number of cells.

[gammaman]

Hello all,

I am trying to count the number of occurances of a range of numbers say (0-5) that can be found within a specific column. Part of the problem is that I do not know The problem is, everyday there is going to be a different size data-set. How can I do this if I do not know the size of the data range. I want to do something like.

count(if(range(a1:a?),"[0-5]"))


So if my column contained the following (0,0,0,1,1,3,3,5,5,6,7,8,9) then the formula would return 9 since there are nine numbers in the data set that are between 0-9.

[jeffreybrown]

One way...

=COUNTIF(A1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),"<=5")

[Marcol]

Another ...

Please Login or Register  to view this content.

Where 0 & 5 are your limits.

Note The list must be continuous, no blanks.

[EDIT]
@ Jeff
Your formula will count negative numbers, but I agree MATCH(99^99, ...) is better than Count() if there could be blanks in the list.

[gammaman]

Thanks to both of you. It works great.

[gammaman]

Just curious, how could I do the same thing for "greater than 5 but less than or equal to 10"?

[jeffreybrown]

Where 0 & 5 are your limits.

Did you change the limits?

[DannyJ]

Also Gammaman,

To clarify, I am sure you worked it out for yourself but it doesn't hurt to say,

Where you were going wrong in your first formula is, to check a whole column you need to type A:A and not A1:A as you did.

Just thought I would make that perfectly clear.

Regards

Danny

[jeffreybrown]

Where you were going wrong in your first formula is, to check a whole column you need to type A:A and not A1:A as you did.

Hi Danny,

Are you proposing the original post would have worked by just changing the A1:A to A:A? Just thinking out loud...

[gammaman]

I got that part working now.

=COUNTIF(J1:INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),"<=10")-COUNTIF(J1:INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),"<=5")

I want to try doing one more thing. Say I have another column of variable length and this column contains characters. How can I search for all of the occurrences of a specific string and subtract it from the result from the formula above?

[jeffreybrown]

Can you provide a mock up example of what you would like to achieve?

As for the formula above you can make it...

=COUNTIFS(J1:INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),">=5",J1:INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),"<=10")

Or you can make a named range

Name: ColJ
Refers To: =Sheet1!$J$1:INDEX(Sheet1!$J:$J,MATCH(9.99999999999999E+307,Sheet1!$J:$J))

=COUNTIFS(ColJ,">=5",ColJ,"<=10")

[gammaman]

Sure. Here is example.

Please Login or Register  to view this content.

I want to count all of the occurrences where the value of column 1 is "ABC" and the value of Column 2 is between 6 and 10. In this example it should return 2.

[jeffreybrown]

Try

Please Login or Register  to view this content.

[gammaman]

Awesome. Works Perfect.

[jeffreybrown]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[Marcol]

Or for 2003 and earlier

Please Login or Register  to view this content.

Dynamically

Please Login or Register  to view this content.

Where A2 is your Code and 6 & 10 your limits

[DannyJ]

Hi Danny,

Are you proposing the original post would have worked by just changing the A1:A to A:A? Just thinking out loud...

Jeffrey,

To clarify I was not saying it would have worked. I was just pointing out that it is A:A not A1:A for anything in the future. I see that how I worded it could have been misleading.

Regards

Danny