COUNTIF problem

[ptm555]

I realise this one's probably small fry for you all, but here goes anyway.

I'm trying to count the number of times certain values occur in range. The values I want to find are:
between 0 and 1 (i.e. less than 1)
between 1 to 2 (i.e. including values of 1.0 but less than 2.0)
between 2 to 3 etc etc

This is the formula I am using for 'between 1 and 2' is:
=COUNTIF($U$2:$U$1323,">=1")-COUNTIF($U$2:$U$1323,">2")

The formula for '0 to 1' is easy and works, but the above formula for the rest is giving me too high a number. What am I doing wrong?

 

[Aladin Akyurek]

I realise this one's probably small fry for you all, but here goes anyway.

I'm trying to count the number of times certain values occur in range. The values I want to find are:
between 0 and 1 (i.e. less than 1)
between 1 to 2 (i.e. including values of 1.0 but less than 2.0)
between 2 to 3 etc etc

This is the formula I am using for 'between 1 and 2' is:
=COUNTIF($U$2:$U$1323,">=1")-COUNTIF($U$2:$U$1323,">2")

The formula for '0 to 1' is easy and works, but the above formula for the rest is giving me too high a number. What am I doing wrong?

That should work too...

See whether the following yields the same result as it should...

=SUMPRODUCT(--($U$2:$U$1323 >=1 ),--($U$2:$U$1323 < 2))

 

[schielrn]

Try:

=sumproduct(--($U$2:$U$1323>=1),--($U$2:$U$1323<2))

You were trying to do >=1 and >2 in your formula and not between.

 

[xenou]

I'm thinking you need >=2 in the second formula to also subtract the 2s. Does that do it? Do you have any blanks in the data, or other potential spoilers?

AB

 

[ptm555]

thanks everyone the sumproduct formula worked