Ask Your Question

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

asked 2014-06-29 18:06:18 +0200

Bed Intruder gravatar image

updated 2015-08-27 12:37:40 +0200

Alex Kemp gravatar image


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".'

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-27 10:51:01.374984


The cells which contain functions probably have boolean type. Everything except 0 is True.

mahfiaz gravatar imagemahfiaz ( 2014-06-30 09:55:54 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2014-06-29 18:37:26 +0200

w_whalley gravatar image

You almost have it. Replace "<>Text" with "<>"&"Text". For other options for the search criterion see the COUNTIF mathematical function in the help file.

edit flag offensive delete link more


Hi, thanks, looking at the helpfile at , it does help... But "<>"&"Text" works in COUNTIF, and not SUMIF, and I dont understand why.

Bed Intruder gravatar imageBed Intruder ( 2014-06-29 19:35:23 +0200 )edit

It worked in my sumif, even with your ranges. Linux and Windows version

w_whalley gravatar imagew_whalley ( 2014-06-29 19:58:35 +0200 )edit

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.

erAck gravatar imageerAck ( 2016-08-29 15:54:59 +0200 )edit

Question Tools

1 follower


Asked: 2014-06-29 18:06:18 +0200

Seen: 22,674 times

Last updated: Jun 29 '14