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

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

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

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

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

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

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

Hi, what is difference between coma and semicolon with the function?

In the Excel help, it suggests

=COUNTIF(A3:A6, "*apples*") (NOTE: coma is used between two arguments)

while =COUNTIF(A3:A6;"*apples*") uses semicolon which is working in my
machine. Does it mean I have to use semicolon to replace coma? Also it
seems many people in the forum used coma rather than semicolon with
various functions.


--
bjg
------------------------------------------------------------------------
bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776
View this thread: http://www.excelforum.com/showthread...hreadid=320037

pure a system configuration issue. My home computer I have with colon (",")
work has semi-colon (";")... just configuration - international and than
seperation symbol (not sure on the exact names here, have a Dutch microsoft
version here)


"bjg" schreef in bericht
...

Hi, what is difference between coma and semicolon with the function?

In the Excel help, it suggests

=COUNTIF(A3:A6, "*apples*") (NOTE: coma is used between two arguments)

while =COUNTIF(A3:A6;"*apples*") uses semicolon which is working in my
machine. Does it mean I have to use semicolon to replace coma? Also it
seems many people in the forum used coma rather than semicolon with
various functions.


--
bjg
------------------------------------------------------------------------
bjg's Profile:
http://www.excelforum.com/member.php...o&userid=16776
View this thread: http://www.excelforum.com/showthread...hreadid=320037

Hi
the reason why you see mostly comas in this forum is that this is an
English forum. Most of all these users (US / UK for example) use the
coma as delimiter. The semicolon is for example used in continental
Europe (e.g. my German Windows has this as default as we use the coma
as decimal separator and not the dot).

But most of all formulas in this forum are posted in the US/UK style
using the coma as delimiter. So if you want to use a posted formula you
have to replace the coma with a semicolon in your worksheet

--
Regards
Frank Kabel
Frankfurt, Germany

"bjg" schrieb im Newsbeitrag
...

Hi, what is difference between coma and semicolon with the function?

In the Excel help, it suggests

=COUNTIF(A3:A6, "*apples*") (NOTE: coma is used between two
arguments)

while =COUNTIF(A3:A6;"*apples*") uses semicolon which is working in
my
machine. Does it mean I have to use semicolon to replace coma? Also
it
seems many people in the forum used coma rather than semicolon with
various functions.


--
bjg
---------------------------------------------------------------------
---
bjg's Profile:
http://www.excelforum.com/member.php...o&userid=16776
View this thread:
http://www.excelforum.com/showthread...hreadid=320037

Hi, thanks for the observation. My question now is, can we reset the
default delimiter , somewhere with Excel option, from semicolon to
coma? Any clue on how to do this is welcome.


--
bjg
------------------------------------------------------------------------
bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776
View this thread: http://www.excelforum.com/showthread...hreadid=320037

Hi
in Excel 2003 you can do this in 'tools - Options - International'.
Before that goto the Windoes Start menu, choose Control Panel -
Regional settings and change it there

--
Regards
Frank Kabel
Frankfurt, Germany

"bjg" schrieb im Newsbeitrag
...

Hi, thanks for the observation. My question now is, can we reset the
default delimiter , somewhere with Excel option, from semicolon to
coma? Any clue on how to do this is welcome.


--
bjg
---------------------------------------------------------------------
---
bjg's Profile:
http://www.excelforum.com/member.php...o&userid=16776
View this thread:
http://www.excelforum.com/showthread...hreadid=320037