countif using a range

  • Thread starter Thread starter gerryR
  • Start date Start date
G

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
 
=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)
 
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
 
that does the trick, thanks Bob!


Bob Phillips said:
=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)
Click to expand...
 
So does the other answer you got, why not try out the solution before posting
back?


Regards,

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

gR
 
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
 
Back
Top