How do I COUNTIF and SUMIF cells whose value is not a specific text?


I am trying to find out how many cells in a column that does not contain a specific text.


This returns “TRUE”, not how many cells is counted.

I also would like to use this with SUMIF


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

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.