Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Countif
HI, I try to replicate an example about COUNTIF (from Excel help) as follows: Suppose A3:A6 contain "apples", "oranges", "peaches", "apples", respectively: COUNTIF(A3:A6,"apples") equals 2 If I use menu Insert/Function, then fill in countif dialog box, i.e. =COUNTIF(A3:A6;"apples"), it ends up 0 which is NOT correct. If I type =COUNTIF(A3:A6;"apples") in some cell, it ends up a windows saying that the formula typed contains an error. I desperately need your advice, as I tried hundreds of times, no any clue on what is wrong. Thanks. -- bjg ------------------------------------------------------------------------ bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776 View this thread: http://www.excelforum.com/showthread...hreadid=320037 |
#2
|
|||
|
|||
Hi
it seems that you have the coma as delimter. So either use: =COUNTIF(A3:A6,"apples") or COUNTIF(A3:A6,"apples") depending on your settings -- Regards Frank Kabel Frankfurt, Germany "bjg" schrieb im Newsbeitrag ... HI, I try to replicate an example about COUNTIF (from Excel help) as follows: Suppose A3:A6 contain "apples", "oranges", "peaches", "apples", respectively: COUNTIF(A3:A6,"apples") equals 2 If I use menu Insert/Function, then fill in countif dialog box, i.e. =COUNTIF(A3:A6;"apples"), it ends up 0 which is NOT correct. If I type =COUNTIF(A3:A6;"apples") in some cell, it ends up a windows saying that the formula typed contains an error. I desperately need your advice, as I tried hundreds of times, no any clue on what is wrong. Thanks. -- bjg --------------------------------------------------------------------- --- bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776 View this thread: http://www.excelforum.com/showthread...hreadid=320037 |
#3
|
|||
|
|||
HI, a good point!! I made several tries again, it seems if we use =COUNTIF(A3:A6,"apples") it ends up a message box, saying the forumlar contain an error. If we use =COUNTIF(A3:A6;"apples") it ends up a wong result which is 0 rather than 2 Any further comments are appreciated. -- bjg ------------------------------------------------------------------------ bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776 View this thread: http://www.excelforum.com/showthread...hreadid=320037 |
#4
|
|||
|
|||
Hi
send me your file :-) But you may also try: =COUNTIF(A3:A6;"*apples*") -- Regards Frank Kabel Frankfurt, Germany "bjg" schrieb im Newsbeitrag ... HI, a good point!! I made several tries again, it seems if we use =COUNTIF(A3:A6,"apples") it ends up a message box, saying the forumlar contain an error. If we use =COUNTIF(A3:A6;"apples") it ends up a wong result which is 0 rather than 2 Any further comments are appreciated. -- bjg --------------------------------------------------------------------- --- bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776 View this thread: http://www.excelforum.com/showthread...hreadid=320037 |
#5
|
|||
|
|||
Have you checked if there are spaces in the original text (before or after
the word "apples" in cell A3:A6)? Has to be an exact match (except for lower / upper case I believe) Johannes "bjg" schreef in bericht ... HI, a good point!! I made several tries again, it seems if we use =COUNTIF(A3:A6,"apples") it ends up a message box, saying the forumlar contain an error. If we use =COUNTIF(A3:A6;"apples") it ends up a wong result which is 0 rather than 2 Any further comments are appreciated. -- bjg ------------------------------------------------------------------------ bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776 View this thread: http://www.excelforum.com/showthread...hreadid=320037 |
#6
|
|||
|
|||
But you may also try: =COUNTIF(A3:A6;"*apples*") Yes, this works. Could you explain to me WHY? -- bjg ------------------------------------------------------------------------ bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776 View this thread: http://www.excelforum.com/showthread...hreadid=320037 |
#7
|
|||
|
|||
Probably Johannes was correct--there were trailing spaces after the text in
A3:A6. The asterisks account for "any more character". For example" *apple* would return in a search Big Apple Granny Smith apple apple pie ... Any text string that contains the word apple. tj "bjg" wrote: But you may also try: =COUNTIF(A3:A6;"*apples*") Yes, this works. Could you explain to me WHY? -- bjg ------------------------------------------------------------------------ bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776 View this thread: http://www.excelforum.com/showthread...hreadid=320037 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal And Countif | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions | |||
Countif ??? | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions | |||
combining countif formulas | Excel Worksheet Functions |