Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
CountIf first column range = "Word" and second column range <> 0 | Excel Worksheet Functions | |||
Countif for specific cells rather than a range ???? | Excel Worksheet Functions |