Ask Your Question

COUNTIF wouldn't recognize criteria - number formated as text [closed]

asked 2013-12-07 14:02:55 +0100

Patrick gravatar image

updated 2013-12-07 14:05:58 +0100


I hope you might be able to help me with a little glitch or misconception of IFCOUNT.

I'm trying to count cells containing version numbers formated as text, like 2.2, 2.3, 2.3.4.

I used =COUNTIF(range; "2.2"), =COUNTIF(range;B1) - B1 containing "2.2" and =COUNTIF(range;&B1) - formating B1, containing "2.2", to text.

Nothing worked so far. I get an empty cell instead of a result number of at least 1 or 0 or just the formular in the cell.

Any ideas what I'm doing wrong? Thanks in advance for your help.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-16 14:24:09.802690


Since there doesn't seem to be an answer, I use a workaround by adding an v as version in front of the unrecognised number/text (-> v2.2 i.e.).

That doesn't solve the problem per se, but serves the purpose.

Question answered = cloesed? Any objections?

Patrick gravatar imagePatrick ( 2013-12-21 14:36:09 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2013-12-07 16:09:22 +0100

m.a.riosv gravatar image

updated 2013-12-08 12:59:03 +0100

Hi Patrick, please what is your LibreOffice version, have you disable Autocalculation? in Menu/Tools/Cells/Autocalculation.

Edited 20131208

For text you need to use double quotes, not single: =COUNTIF(range;"2.2")
Comparator must be part of text: =COUNTIF(range;"=2.2")

edit flag offensive delete link more

answered 2013-12-08 11:46:41 +0100

Patrick gravatar image

Thank you for your quick answer.

I'm using LibreOffice 4.1.x ( to be more specific, but I tried other iterations as well. No luck so far.)

Autocalculation was enabled, but disabling it, didn't change the behaviour of the cell.

By accident I could trigger an Error:510 response by changing the column width, instead of an empty cell. So an operator is missing, but that doesn't make any sense, since I'm parsing strings instead of numbers.

My guess is, if I'm not using the semantic wrong, LibreOffice gets confused by the decimal point which isn't one. Besides I'm using either German or English user interfaces, which adds another fumble, handling decimal-points or decimal-commas.

Well, I added

  • =COUNTIF(range;'2.2')
  • =COUNTIF(range;='2.2')
  • =COUNTIF(range;=B2) // B2 yields 2.2 formatted as text
  • =COUNTIF(range;=&B2) // B2 yields 2.2 formatted as text

to my trail and error list. Same results. Either an empty cell or Error:510 after resizing the cell.

ZÄHLEWENN() funktioniert auch nicht. (DE)

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-12-07 14:02:55 +0100

Seen: 3,683 times

Last updated: Dec 08 '13