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

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.