# COUNTIF problem

#### ptm555

##### New Member
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?

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
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

##### New Member
thanks everyone the sumproduct formula worked

Replies
3
Views
238
Replies
7
Views
422
Replies
6
Views
489
Replies
1
Views
408
Replies
4
Views
301

1,190,749
Messages
5,982,728
Members
439,792
Latest member
vram

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back