Please help, I can't figure out why countifs( isn't working

The formula is working perfectly. Sort the data by size and highlight the data equal to or greater than 51.0 and LESS than or equal to 61.0 This is exactly 134.

Hi Blackhawks,

You're count seems like incorrect because there are decimals in the range. the top 69 will not count 69.123 and so on.

I think the problem is that you have gaps and overlaps in your ranges, coupled with the fact that your numbers are not integers.

For example, you have "<50" but then the next range is 51-69. So what happens to numbers greater than 50 but less than 51 ... there are 4 of them.

And then you have a range 80-90, and another, 90-100. So where do you plan to count 90? There's only 1 of it, fortunately.

You should have formulae like:

Formula:

=COUNTIFS($B$6:$B$656,">=1",$B$6:$B$656,"<51")

and

Formula:

=COUNTIFS($B$6:$B$656,">=51",$B$6:$B$656,"<71")

and

Formula:

=COUNTIFS($B$6:$B$656,">=71",$B$6:$B$656,"<91")

for example

Regards, TMS

=COUNTIFS(B$6:B$656,"<=50") 23
=COUNTIFS(B$6:B$656,">50",B$6:B$656,"<70") 157
=COUNTIFS(B$6:B$656,">=70",B$6:B$656,"<80") 144
=COUNTIFS(B$6:B$656,">=80",B$6:B$656,"<90") 165
=COUNTIFS(B$6:B$656,">=90",B$6:B$656,"<=100") 162
total = 651
this includes all values you may have to adjust boundaries to suit

	D	E	F
9	< 50	22	=COUNTIFS(A$6:A$656,"<50")
10	51 - 69	158	=COUNTIFS(A$6:A$656,">=50",A$6:A$656,"<70")
11	70 - 79	144	=COUNTIFS(A$6:A$656,">=70",A$6:A$656,"<80")
12	80 - 90	165	=COUNTIFS(A$6:A$656,">=80",A$6:A$656,"<90")
13	90 - 100	162	=COUNTIFS(A$6:A$656,">=90",A$6:A$656,"<=100")
14	Total	651

trouble with frequency as you seemed to have used the data is put into the next highest bin if it can match exactly
eg data
1
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
2
and you use bins
1
2
then the result is bin 1=1 bin 2 is 10

Here is your workbook and it illustrates the problems that are described above. I sorted the data in order, numbered the values as indicated in your value ranges and amended the formulae to accommodate the decimal overlaps (which is the main problem amongst others.

Take a look and what the others have said will be amply illustrated.