[Dropped] This simplest of formulas does not work

[CaliRay]

Code: Select all

=COUNTIF(J2:J37;'Sheep')

this one does not work either

Code: Select all

=COUNTIF(J2:J37;"Sheep")

The cells in the range are formatted as text and the formula is copied directly from the tutorial page for COUNTIF, replacing the range and target with my own values.

I also checked settings and unchecked case sensitive. Screenshot attached.

[robleyd]

A quick test suggests that it will work for me; perhaps you could upload a sample .ods file that shows the problem you are having. Without an example, we are guessing.

One guess - you have trailing spaces in the search range.

[RoryOF]

Attached is my example, which works OK when I use " delimiters about the search term.

Edit: Check that you are using straight quotes, not curly quotes.

[Villeroy]

The most likely reason is that there is no cell value "Sheep" but "Sheep " or " Sheep" with white space.

[njhub]

Try with :

Code: Select all

=COUNTIF(J2:J37,"*Sheep*")

[Zizi64]

CaliRay, please upload your ODF type sample file here.

[Villeroy]

Try with :

Code: Select all

=COUNTIF(J2:J37,"*Sheep*")

CaliRay uses OpenOffice.
OpenOffice supports regular expressions but no wildcards.
OpenOffice does not support comma as list separator.
-----------------
A simple solution: Disable "Search criteria ... match whole cell" In the calculation options.
Another solution: Enable regular expressions in formulas and use them like this: =COUNTIF(J2:J37,".*Sheep.*")