Hello
I am trying to find out how many cells in a column that does not contain a specific text.
=COUNTIF(B1:B1002;"<>Text")
This returns “TRUE”, not how many cells is counted.
I also would like to use this with SUMIF
=SUMIF(B1:B1002;"<>Text";D1:D9999)
But this also returns true, and not the sum of all cells in D where B column has not “text”.’
The cells which contain functions probably have boolean type. Everything except 0 is True.
You almost have it. Replace "<>Text" with "<>"&"Text". For other options for the search criterion see the COUNTIF mathematical function in the help file.
Hi, thanks, looking at the helpfile at Mathematical Functions - LibreOffice Help, it does help… But "<>"&"Text" works in COUNTIF, and not SUMIF, and I dont understand why.
It worked in my sumif, even with your ranges. Linux and Windows version 4.2.5.2.
Using the & operator here is completely unnecessary, the result is identical to the literal “<>Text”. The correct answer is that the cell has a Boolean number format applied.