=COUNTIF off by 1 in total?

Badunit wrote:
Redress is not a match. It would be a match to red with stars on each side.

Nor are:

the red shoes
the shoes are red.
red
(the example above is "red " (note the space following the word)

Regards,
Barry

You may want to look more closely at the words in each cell to ensure that they actually match.

Red is a match
Warred is a match
Redress is not a match. It would be a match to red with stars on each side.

COUNTIF =0 if none, =1 if one, etc. However, if "red" occurs twice within the same cell it is counted only once.

The result for "*red" is correct. It is counting the first row only. The second row is not a match.

The result for "*blue" is incorrect unless there is a space or something after "blue" in row 3. Are you sure there is not a space or anything else after the word "blue" in "the sky is blue"?

Jon Canfield wrote:
Thanks for the replies. I do understand the wildcards and exact matching. I have 3 different items"
The sky is red
see the red tree
the sky is blue

=COUNTIF(B1::B10,"*blue") returns 0

adding an additional cell:
the sky is blue

Hi Jon,

What does "adding an additional cell" mean? What cell is added? To what is it added?
Before adding that cell, what result is returned by:

=COUNTIF(B1::B10," blue")
=COUNTIF(B1::B10," sky")

Do these results change after adding the additional cell?

Regards,
Barry
Answers to those questions may help us (or you) determine what the issue is.

It seems clear that in the original cell there is an extraneous character (space ? ) just after the word 'blue' and that there isn't such one in the added cell.

Yvan KOENIG (VALLAURIS, France) mercredi 30 mars 2011 10:11:07

I wish I knew what was going on. It works correctly for me. A space at the end of the sentence or at the end of the search word makes a difference and the result is always the expected one. If you would like to send me the document, I'll take a look at it. Click on my name to the left to get my email address. Otherwise,

If you do =LEN() of each of these cells, do you get the correct number of letters you expect?

Have you tried deleting the contents of the cell(s) (select the cell, not the text in the cell, and press delete) and retyping the sentences?

Have you tried creating a brand new document and trying it there? Maybe there's a glitch in this particular document or table.

Are the cells in question within the range B1::B10? I hesitate to ask something so obvious but I'm at a loss to explain the problem.

The formula =COUNTIF(B1::B10,"blue") would return a value of 1 only if there was a cell in the range that contains only the word blue - it won't use a partial string, which is why I went with the * wildcard. I also don't know where in the string the word will be, so I can't use the ? or follow the word with a wildcard because it may be in the middle or end of a string.

The search word "*blue" will not find "blue" in the middle of a string. It will only count if "blue" is at the end of the string. Though it seems to be giving you problems even with that.

Message was edited by: Badunit

Jon Canfield wrote:
Barry -

Adding a space or removing one after the search word or red or blue does not change the results. If I change the formula to =COUNTIF(B1::B10,"*red "), it then returns 0 regardless of the presence of a space after the word red.

Does not match my results. Adding the space as shown above will remove any instances where the string in B1::B10 does not end with the four characters "red ". See below.

The formula =COUNTIF(B1::B10,"blue") would return a value of 1 only if there was a cell in the range that contains only the word blue - it won't use a partial string, which is why I went with the * wildcard. I also don't know where in the string the word will be, so I can't use the ? or follow the word with a wildcard because it may be in the middle or end of a string.

My results indicate the * wildcard accepts strings with no characters following the search string, no characters preceding the search string, or both.

You are getting some odd results, and I am beginning to suspect:
– a corrupted document. (Try with a new document.)
– corrupted preferences, or some other problem specific to the user account (try the same document opened in a different user account)
- a system update using the incremental updater (ie. done via Software Update) (Download and apply the Combo update) (If you're using Snow Leopard, and had indicated this in the profile line, this possibility would likely have been suggested earlier.)

Here's my current nine item sample. x indicates the presence of a space in the actual entry.

the sky is red
the sky is redx
the red shoe
red headed girl
xred headed girl
red-haired girl (later edited to red-headed girl with no change to count, except as noted below)
black haired girl
red
redx

=COUNTIF($A,"red") returned 1 (item 8)
=COUNTIF($A,"*red") returned 2 (items 1 and 8)
=COUNTIF($A," red") returned 9 (counted all items, including those beginning and/or ending with "red")

=COUNTIF($A,"red ") returned 1 (item 9)
=COUNTIF($A,"*red ") returned 2 (items 2 and 9)
=COUNTIF($A,"*red *") returned 7, then 6 (omitted items 1 and 8, and after editing, 7)




Column B shows the first three and last three characters in the string in column A, joined by two hyphens and bounded by two pipes. Its purpose is to indicate the cells with leading or trailing spaces.

Regards,
Barry

I'll take a look at it. In the mean time, your solution may require several COUNTIF functions. The necessary conditions for COUNTIF under various scenarios are:

Red as the entire string: "red"

Red as the beginning of the string: "red *"

Red in the middle of the string: "* red *"

Red at the end of the string (and no period at the end) "* red"

I did not test a function that has all four of these COUNTIF statements to ensure it worked as I intended so no guarantees. I don't think any of these conditions duplicates another or allows a word other than "red" to be counted. You may also want to add a condition for "* red,*"

EDIT: I looked at your document and the results are correct for all of your COUNTIF statements. Just in case you had different results than what I see, the results are 2,3,2,1 and 5,5,3,5.

Message was edited by: Badunit