countif using a range

[gerryR]

Hi All

Trying to use countif on a range, say my data is below:

5.4
5.3
5.1
5.1
5
4.9
4.9
4.3
4
3.8

I want to have the following

5+ 5
4-5 4
etc...

for the 1st one its no prob, I have the following countif
=COUNTIF(CompDbl!C2:C96,">=5")

but for the next one and below I need to be able to count if the value is
between 2 values, I tried this but no joy:
=COUNTIF(CompDbl!C2:C96,">=4"&"<=4.99")

I'd appreciate any help anyone can provide, can this even be done using
countif??

thanks
gR

 

[Ardus Petus]

=SUMPRODUCT(CompDb1:C2:C96>=4)*(CompDb1:C2:C96<5)

HTH

 

[Bob Phillips]

=COUNTIF(A:A,">=5")

and

=COUNTIF(A:A,">=4")-COUNTIF(A:A,">=5")

etc.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[gerryR]

thanks for the reply, unfortunatly it don't seem to do the trick. What the
column (containing 5.4, 5.3 etc) is refering to is age, I need to count how
many are 5 (ie how many 5 occurs). how many are 4 etc. Is SUMProduct not
multiplying them?

thanks again for the suggestion, if you've any more ideas I'd appreciate
them.
gR

 

[gerryR]

that does the trick, thanks Bob!

=COUNTIF(A:A,">=5")

and

=COUNTIF(A:A,">=4")-COUNTIF(A:A,">=5")

etc.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

So does the other answer you got, why not try out the solution before posting
back?


Regards,

Peo Sjoblom

 

[gerryR]

I did try it, it didn't work other wise I wouldn't have replied saying it
didn't work.

gR

 

[Ardus Petus]

There was a typo. Try this:
=SUMPRODUCT((CompDb1:C2:C96>=4)*(CompDb1:C2:C96<5))

SUMPRODUCT first multiplies boolean values, giving 0 ou 1.
Then it adds up those results.
Which produces the count of rows where both conditions are TRUE.

HTH