Countif

[bjg]

HI, I try to replicate an example about COUNTIF (from Excel help) a
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 window
saying that the formula typed contains an error.

I desperately need your advice, as I tried hundreds of times, no an
clue on what is wrong. Thanks

 

[Frank Kabel]

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

 

[bjg]

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.

 

[Frank Kabel]

Hi
send me your file

But you may also try:
=COUNTIF(A3:A6;"*apples*")

 

[Johannes]

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]

But you may also try:
=COUNTIF(A3:A6;"*apples*")

Yes, this works. Could you explain to me WHY

 

[tjtjjtjt]

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]

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 m
machine. Does it mean I have to use semicolon to replace coma? Also i
seems many people in the forum used coma rather than semicolon wit
various functions

 

[Johannes]

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)

 

[Frank Kabel]

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

 

[bjg]

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

 

[Frank Kabel]

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