COUNTIF

S

Simon Minder

Hi all,

I have a huge data range with values between -5 and +4. I would like to
count the values with the following groups:
-5 to -4
-4 to -3
-3 to -2
-2 to -1
-1 to 0
0 to 1
1 to 2
2 to 3
3 to 4

e.g. The value 0.5 would go into the group "0 to 1". The value 1 would still
go into the group "0 to 1" and not into the group "1 to 2".

I tried to find a workable solution with the COUNTIF function, but I had no
luck.

Does anybody know a solution for this problem?

Kind regards,

Simon Minder
 
T

Toppers

Try:

=COUNTIF($A$1:$A$50,">" & B1)-COUNTIF($A$1:$A$50,">"& C1)

where B1=0, C1=1 to give results for 0 to 1 (0.0001 to 1.0000)

HTH
 
B

Bearacade

Assuming the A1:A100 is your data.

B1 = '-5 to -4
B2 = '-4 to -3
B3 = '-3 to -2
B4 = '-2 to -1
B5 = '-1 to 0
B6 = '0 to 1
B7 = '1 to 2
B8 = '2 to 3
B9 = '3 to 4

C1 = =COUNTIF(A1:A100,">-5")-COUNTIF(A1:A100,">-4")
C2 = =COUNTIF(A1:A100,">-4")-COUNTIF(A1:A100,">-3")
C3 = =COUNTIF(A1:A100,">-3")-COUNTIF(A1:A100,">-2")
C4 = =COUNTIF(A1:A100,">-2")-COUNTIF(A1:A100,">-1")
C5 = =COUNTIF(A1:A100,">-1")-COUNTIF(A1:A100,">0")
C6 = =COUNTIF(A1:A100,">0")-COUNTIF(A1:A100,">1")
C7 = =COUNTIF(A1:A100,">1")-COUNTIF(A1:A100,">2")
C8 = =COUNTIF(A1:A100,">2")-COUNTIF(A1:A100,">3")
C9 = =COUNTIF(A1:A100,">3")-COUNTIF(A1:A100,">4")

Hope that helps
 
S

Simon Minder

Hi Bearacade,

Thank you very much for your support.

I only had to replace the comma by a ';' and everything worked. See below:

=COUNTIF(A1:A100;">-5")-COUNTIF(A1:A100;">-4")
=COUNTIF(A1:A100;">-4")-COUNTIF(A1:A100;">-3")
=COUNTIF(A1:A100;">-3")-COUNTIF(A1:A100;">-2")
=COUNTIF(A1:A100;">-2")-COUNTIF(A1:A100;">-1")
=COUNTIF(A1:A100;">-1")-COUNTIF(A1:A100;">0")
=COUNTIF(A1:A100;">0")-COUNTIF(A1:A100;">1")
=COUNTIF(A1:A100;">1")-COUNTIF(A1:A100;">2")
=COUNTIF(A1:A100;">2")-COUNTIF(A1:A100;">3")
=COUNTIF(A1:A100;">3")-COUNTIF(A1:A100;">4")

Kind regards,

Simon Minder
 
Top