Please help with Countif, greater than, less than

[sbost]

Seems like an easy request. I have a column of numbers of varying values. I want to count all of the values that are greater than or equal to 0 but less than 3; then, in a new cell, all of the values that are greater than or equal to 3 but less than 6; and so on.

At the bottom of the column, I put in COUNTIF(b2:b52,">=0,<3"), and the result?? Zero. Even though I can count numerous 0s, 1s, and 2s just by looking. Or else it tells me I've entered "too many arguments for this function."

How should I go about programming this???? Please help!! Thanks. SB

 

[Richard Schollar]

Hi

Check out Sumproduct:

=SUMPRODUCT(($B$2:$B$52>=0)+0,($B$2:$B$52<0)+0)

and so on.

 

[Von Pookie]

The COUNTIF function can only do one operation. So either greater than 0 or less than 3, but not both.

Try SUMPRODUCT instead:

=SUMPRODUCT(--(B2:B52<>"")*(B2:B52>=0)*(B2:B52<3))

 

[Erdinç E. Karaçam]

Hi,

Use Sumproduct if you need multiple criterias for Countif.

=SUMPRODUCT((B1:B52>=0)*(B1:B52<=3))

 

[Brian from Maui]

Seems like an easy request. I have a column of numbers of varying values. I want to count all of the values that are greater than or equal to 0 but less than 3; then, in a new cell, all of the values that are greater than or equal to 3 but less than 6; and so on.

At the bottom of the column, I put in COUNTIF(b2:b52,">=0,<3"), and the result?? Zero. Even though I can count numerous 0s, 1s, and 2s just by looking. Or else it tells me I've entered "too many arguments for this function."

How should I go about programming this???? Please help!! Thanks. SB

Using Countif,

=COUNTIF(B2:B52>=0)-COUNTIF(B2:B52 > 3)