multiple data ranges for countif
 

I am trying to find the number of times my value falls in a certain range.
the data looks like this:
Clg (Tons)
500.6
486.9
473.4
465.8
469.8
510.1
497.5
538.9
623.5
688.5
671.8
687.3
673.9
690.9
716.5
713.7
704.6
665.8
606.3
599.9
582.3
497.4
498.8
478.8

and my different ranges would be in one hundred increments (i.e. 400-500,
501-600,601-700,etc.)
Any help would be great

multiple data ranges for countif
 

=COUNTIF(A2:A1000,""&B2)-COUNTIF(A2:A1000,""&C2)

B2=401, B3=501 etc
C2=500, C3=600 etc

HTH

"David G" wrote:

I am trying to find the number of times my value falls in a certain range.
the data looks like this:
Clg (Tons)
500.6
486.9
473.4
465.8
469.8
510.1
497.5
538.9
623.5
688.5
671.8
687.3
673.9
690.9
716.5
713.7
704.6
665.8
606.3
599.9
582.3
497.4
498.8
478.8

and my different ranges would be in one hundred increments (i.e. 400-500,
501-600,601-700,etc.)
Any help would be great

multiple data ranges for countif
 

"David G" wrote...
I am trying to find the number of times my value falls in a certain range.
the data looks like this:
Clg (Tons)
500.6
486.9
....
and my different ranges would be in one hundred increments (i.e. 400-500,
501-600,601-700,etc.)
....

This is what the FREQUENCY function was designed to do. If you have ranges
from 0 to =100, 100 to =200, . . ., 1900 to =2000 and your data were in a
range or worksheet cells named DATA, select a 1-column by 21-row range,
e.g., X2:X22, type the formula

=FREQUENCY(DATA,100*ROW(1:20))

hold down [Ctrl] and [Shift] keys and press [Enter]. This should enter this
formula as an array formula, with the count of 0-100 values in X2, the count
of 100-200 values in X3, etc. Cell X22 will show the number of values that
exceed 2000.

This could be done with COUNTIF, e.g., for the 300 to =400 value range

=COUNTIF(DATA,"300")-COUNTIF(DATA,"400")

but FREQUENCY handles consecutive ranges in a single batch operation, MUCH
more efficient.

multiple data ranges for countif
 

=countif(A:A,500,)-countif(A:A,600)
=countif(A:A,600,)-countif(A:A,700)

and so on...

Can also do this as a pivot table. drop your values in column and in data.
Right click in the column field, select Group and Outline, Group, have it
group by increments of 100 starting at 500...


"David G" wrote:

I am trying to find the number of times my value falls in a certain range.
the data looks like this:
Clg (Tons)
500.6
486.9
473.4
465.8
469.8
510.1
497.5
538.9
623.5
688.5
671.8
687.3
673.9
690.9
716.5
713.7
704.6
665.8
606.3
599.9
582.3
497.4
498.8
478.8

and my different ranges would be in one hundred increments (i.e. 400-500,
501-600,601-700,etc.)
Any help would be great

multiple data ranges for countif
 

"Toppers" wrote...
=COUNTIF(A2:A1000,""&B2)-COUNTIF(A2:A1000,""&C2)

B2=401, B3=501 etc
C2=500, C3=600 etc
....

Classic mistake. Note that the OP's VERY FIRST data point was 500.6.
However, using your intervals above, the formula for the next range using
your formula above would be

=COUNTIF(A2:A1000,""&B3)-COUNTIF(A2:A1000,""&C3)

which would be equivalent to

=COUNTIF(A2:A1000,"501")-COUNTIF(A2:A1000,"600")

So both your original formula and my extension of it to the next interval
would skip the value 500.6. It would have been safer to use just one set of
bounds

B2: 400
B3: 500
B4: 600

and formulas like

=COUNTIF(DATA,""&B2)-COUNTIF(DATA,""&B3)

=COUNTIF(DATA,""&B3)-COUNTIF(DATA,""&B4)

The second of these would include 500.6 in its count. I really doubt the OP
would have wanted to exclude data points for which 0 < MOD(x,100) < 1.