| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
countif results
Hello Guys..
I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#2
|
|||
|
|||
|
"True" (in quotes) means literal text. The text isn't literal. Rather "true"
in this case is a value. So, you dont' need the quotes in your formula. =Countif(A10:A20,TRUE) ******************* ~Anne Troy www.OfficeArticles.com "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#3
|
|||
|
|||
|
Hello Anne -
That doesn't work as I had already tried that......still returns 0. What it is about True that causes this problem? In the IF formula when I change the text "True" to the boolean TRUE then use - =Countif(A10:A20,TRUE) I get the correct result. I guess I can use the boolean value but would still like to know why True doesn't get recognized ????? Thanks "Anne Troy" wrote: "True" (in quotes) means literal text. The text isn't literal. Rather "true" in this case is a value. So, you dont' need the quotes in your formula. =Countif(A10:A20,TRUE) ******************* ~Anne Troy www.OfficeArticles.com "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#4
|
|||
|
|||
|
Why would you even type in true in an if formula? E.g.
=IF(A12,"True","False") can be written =A12 instead, there is no need to write true or false -- Regards, Peo Sjoblom (No private emails please) "Lookin for a job" wrote in message ... Hello Anne - That doesn't work as I had already tried that......still returns 0. What it is about True that causes this problem? In the IF formula when I change the text "True" to the boolean TRUE then use - =Countif(A10:A20,TRUE) I get the correct result. I guess I can use the boolean value but would still like to know why True doesn't get recognized ????? Thanks "Anne Troy" wrote: "True" (in quotes) means literal text. The text isn't literal. Rather "true" in this case is a value. So, you dont' need the quotes in your formula. =Countif(A10:A20,TRUE) ******************* ~Anne Troy www.OfficeArticles.com "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#5
|
|||
|
|||
|
=IF(A12,"True","False")
can be written =A12 Yes it can but I don't want a FALSE return. I want "". I can use the boolean TRUE - =IF(A12,True,"") But why doesn't "true" get recognized? What's the difference between "apples" and "true" in a Countif? Seems to me there is none ??? Thanks "Peo Sjoblom" wrote: Why would you even type in true in an if formula? E.g. =IF(A12,"True","False") can be written =A12 instead, there is no need to write true or false -- Regards, Peo Sjoblom (No private emails please) "Lookin for a job" wrote in message ... Hello Anne - That doesn't work as I had already tried that......still returns 0. What it is about True that causes this problem? In the IF formula when I change the text "True" to the boolean TRUE then use - =Countif(A10:A20,TRUE) I get the correct result. I guess I can use the boolean value but would still like to know why True doesn't get recognized ????? Thanks "Anne Troy" wrote: "True" (in quotes) means literal text. The text isn't literal. Rather "true" in this case is a value. So, you dont' need the quotes in your formula. =Countif(A10:A20,TRUE) ******************* ~Anne Troy www.OfficeArticles.com "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#6
|
|||
|
|||
|
Hi,
Since you are pretty confident that True is text and not a logical value, why don't you try something like this (just in case there is an extra space o something): =COUNTIF(A10:A20,"*True*") Regards, KL "Lookin for a job" wrote in message ... Hello Anne - That doesn't work as I had already tried that......still returns 0. What it is about True that causes this problem? In the IF formula when I change the text "True" to the boolean TRUE then use - =Countif(A10:A20,TRUE) I get the correct result. I guess I can use the boolean value but would still like to know why True doesn't get recognized ????? Thanks "Anne Troy" wrote: "True" (in quotes) means literal text. The text isn't literal. Rather "true" in this case is a value. So, you dont' need the quotes in your formula. =Countif(A10:A20,TRUE) ******************* ~Anne Troy www.OfficeArticles.com "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#7
|
|||
|
|||
|
That works and I'm absolutely certain that the IF formula uses the text value
"true" and not the boolen value TRUE and there are no extra chars in the cells. I really appreciate everyones input and suggestions but does anybody know WHY - =Countif(A10:20,"true") Thanks "KL" wrote: Hi, Since you are pretty confident that True is text and not a logical value, why don't you try something like this (just in case there is an extra space o something): =COUNTIF(A10:A20,"*True*") Regards, KL "Lookin for a job" wrote in message ... Hello Anne - That doesn't work as I had already tried that......still returns 0. What it is about True that causes this problem? In the IF formula when I change the text "True" to the boolean TRUE then use - =Countif(A10:A20,TRUE) I get the correct result. I guess I can use the boolean value but would still like to know why True doesn't get recognized ????? Thanks "Anne Troy" wrote: "True" (in quotes) means literal text. The text isn't literal. Rather "true" in this case is a value. So, you dont' need the quotes in your formula. =Countif(A10:A20,TRUE) ******************* ~Anne Troy www.OfficeArticles.com "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#8
|
|||
|
|||
|
Try this
=SUMPRODUCT(--(A10:A20="TRUE")) Does it help? "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#9
|
|||
|
|||
|
That certainly will work but the Countif is more efficient.
I changed the text true to the boolean TRUE. I really appreciate everyones help but can anyone answer the question as to why - =Countif(A10:A20,"true") returns 0 ??? Thanks "kk" wrote: Try this =SUMPRODUCT(--(A10:A20="TRUE")) Does it help? "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#10
|
|||
|
|||
|
Okay. I think you'd better give us the exact formulas in your A10 to A20
'cause I can't replicate the problem you're having. Works for me whether "TRUE" or TRUE. ******************* ~Anne Troy www.OfficeArticles.com "Lookin for a job" wrote in message ... That certainly will work but the Countif is more efficient. I changed the text true to the boolean TRUE. I really appreciate everyones help but can anyone answer the question as to why - =Countif(A10:A20,"true") returns 0 ??? Thanks "kk" wrote: Try this =SUMPRODUCT(--(A10:A20="TRUE")) Does it help? "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#11
|
|||
|
|||
|
Hmm... not for me (XL03/04).
Using =COUNTIF(A10:A20,TRUE) or =COUNTIF(A10:A20,"TRUE") fails to count cells in A10:A20 containing Text "TRUE". OTOH this counted Text "TRUE" but not boolean TRUE: =COUNTIF(A10:A20,"TRUE*") In article , "Anne Troy" wrote: Okay. I think you'd better give us the exact formulas in your A10 to A20 'cause I can't replicate the problem you're having. Works for me whether "TRUE" or TRUE. |
|
#12
|
|||
|
|||
|
Have a look see at this screencap -
http://img177.imageshack.us/img177/9371/countif4tc.jpg It seems aparent to me that Countif doesn't like a text "True". I'm just wanting to know why. What's the difference between - =Countif(A10:A20,"apples") and =Countif(A10:A20,"True") Thanks "Anne Troy" wrote: Okay. I think you'd better give us the exact formulas in your A10 to A20 'cause I can't replicate the problem you're having. Works for me whether "TRUE" or TRUE. ******************* ~Anne Troy www.OfficeArticles.com "Lookin for a job" wrote in message ... That certainly will work but the Countif is more efficient. I changed the text true to the boolean TRUE. I really appreciate everyones help but can anyone answer the question as to why - =Countif(A10:A20,"true") returns 0 ??? Thanks "kk" wrote: Try this =SUMPRODUCT(--(A10:A20="TRUE")) Does it help? "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#13
|
|||
|
|||
|
Wow! That's weird. I can reproduce this in XL2K.
KL "Lookin for a job" wrote in message ... Have a look see at this screencap - http://img177.imageshack.us/img177/9371/countif4tc.jpg It seems aparent to me that Countif doesn't like a text "True". I'm just wanting to know why. What's the difference between - =Countif(A10:A20,"apples") and =Countif(A10:A20,"True") Thanks "Anne Troy" wrote: Okay. I think you'd better give us the exact formulas in your A10 to A20 'cause I can't replicate the problem you're having. Works for me whether "TRUE" or TRUE. ******************* ~Anne Troy www.OfficeArticles.com "Lookin for a job" wrote in message ... That certainly will work but the Countif is more efficient. I changed the text true to the boolean TRUE. I really appreciate everyones help but can anyone answer the question as to why - =Countif(A10:A20,"true") returns 0 ??? Thanks "kk" wrote: Try this =SUMPRODUCT(--(A10:A20="TRUE")) Does it help? "Lookin for a job" <Lookin for a wrote in message ... Hello Guys.. I have a range of IF formulas in A10:A20 that return either a text "True" (not a boolean) or a blank "" . There are several "Trues" in the range but when I use - =Countif(A10:A20,"True") I get a result of 0. Why is this? Thanks |
|
#14
|
|||
|
|||
|
I'm certainly not an authority, but I'll take a guess. The
implementation of COUNTIF() parses the comparison argument as a string, so =COUNTIF(A10:A20,"=" & B1) will interpret the concatenated string and attempt to deduce the argument type from the context. When a comparison operator is left out, "=" is implied, so just as =COUNTIF(A10:A20,10) is syntactically equivalent to =COUNTIF(A10:A20,"=10") So =COUNTIF(A10:A20,"True") is syntactically equivalent to =COUNTIF(A10:A20,"=True") Obviously, the function parser will preferentially interpret arguments as numbers or booleans rather than strings, so the boolean will be matched. However, when you use a wildcard: =COUNTIF(A10:A20,"TRUE*") it forces the parser to interpret the argument as a string, and thus match In article , "Lookin for a job" wrote: What's the difference between - =Countif(A10:A20,"apples") and =Countif(A10:A20,"True") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculating results in formulas | Excel Discussion (Misc queries) | |||
| Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
| Countif - Countif | Excel Worksheet Functions | |||
| ?odd results for =left(F#,2) | Excel Worksheet Functions | |||
| countif, again | Excel Worksheet Functions | |||
Hybrid Mode
