Ask Your Question
1

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

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

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

Comments

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
-1

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

Comments

Hi, thanks, looking at the helpfile at https://help.libreoffice.org/Calc/Mat... , 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 4.2.5.2.

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

Stats

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

Seen: 22,674 times

Last updated: Jun 29 '14