countif using a range
 

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 using a range
 

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

HTH
--
AP

"gerryR" a écrit dans le message de news:
...
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 using a range
 

=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" wrote in message
...
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 using a range
 

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


"Ardus Petus" wrote in message
...
=SUMPRODUCT(CompDb1:C2:C96=4)*(CompDb1:C2:C96<5)

HTH
--
AP

"gerryR" a écrit dans le message de news:
...
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 using a range
 

that does the trick, thanks Bob!


"Bob Phillips" wrote in message
...
=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" wrote in message
...
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 using a range
 

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


Regards,

Peo Sjoblom

"gerryR" wrote:

that does the trick, thanks Bob!


"Bob Phillips" wrote in message
...
=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" wrote in message
...
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 using a range
 

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

gR


"Peo Sjoblom" wrote in message
...
So does the other answer you got, why not try out the solution before
posting
back?


Regards,

Peo Sjoblom

"gerryR" wrote:

that does the trick, thanks Bob!


"Bob Phillips" wrote in message
...
=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" wrote in message
...
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 using a range
 

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
--
AP

"gerryR" a écrit dans le message de news:
...
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


"Ardus Petus" wrote in message
...
=SUMPRODUCT(CompDb1:C2:C96=4)*(CompDb1:C2:C96<5)

HTH
--
AP

"gerryR" a écrit dans le message de news:
...
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