Ask Your Question

Why does "" not work as criterion for SUMIF? [closed]

asked 2013-03-22 06:59:26 +0200

stuckfly gravatar image

Where: A1:A100 is a range of values, and B1:B100 is a range of either "x" or "" I want to sum values in A1:A100 for cells with empty strings "", so: =SUMIF(B1:B100;"";A1:A100)

Problem: it sums no values; the answer is 0, regardless of what is in B1:B100.

Yet, this method works fine in Excel (I know, OpenOffice is not Excel), and this method works fine in formulae that test only one cell. For example: in C1 the formula =B1="" will produce TRUE. Ditto for =IF(B1="";"TRUE";"FALSE")

So, why does an empty string work in a single test, but not for a range?

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 2015-10-27 12:28:16.420810


Try workaround =SUM(A1:A100)-SUMIF(B1:B100;">";A1:A100) or =SUMPRODUCT(A1:A100;B1:B100="") or {=SUM(A1:A100*(B1:B100=""))}

JohnSUN gravatar imageJohnSUN ( 2013-03-22 08:46:51 +0200 )edit

=SUM(A1:A100) - SUMIF(B1:B100;"x";A1:A100)

B.Horvat gravatar imageB.Horvat ( 2013-03-22 09:08:16 +0200 )edit

"x" - equal to letter x, ">" - greater empty string, contains any value

JohnSUN gravatar imageJohnSUN ( 2013-03-22 09:38:08 +0200 )edit

@JohnSUN, I agree. :)

B.Horvat gravatar imageB.Horvat ( 2013-03-22 19:31:25 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2013-03-22 11:22:20 +0200

mahfiaz gravatar image

I don't know why this doesn't work, it would be nice if it would. Please write a bug report about it.

But what works is this:

edit flag offensive delete link more


"<>" or ">" is "not equal empty string"... "Empty string" - disputable concept. The cell with the formula = IF (0; ""; "") appears as a blank, but then there are the contents? ="" return TRUE, ISBLANK() return FALSE. Which of these results is correct? Do not think you need to report a bug. It isn't bug... Enough to know regarding the behavior of the program.

JohnSUN gravatar imageJohnSUN ( 2013-03-22 11:40:46 +0200 )edit

@JohnSUN, it might be a bug just because it's different from what Excel does. I'm not saying it necessarily is.

A little correction: ">" is "Greater than" (leaves negative values and zeroes out)

mahfiaz gravatar imagemahfiaz ( 2013-03-22 12:07:51 +0200 )edit

BTW, it is already reported: fdo#35636

mahfiaz gravatar imagemahfiaz ( 2013-03-22 12:11:55 +0200 )edit

@mahfiaz Maybe it's a bug of Excel? ;-)

JohnSUN gravatar imageJohnSUN ( 2013-03-22 12:13:53 +0200 )edit

@JohnSUN, you might be right :)

But as it turns out there right now is no oneliner to sum if another cell is really empty (not just empty string) and this would be true for only empty strings as well if someone decides to make it work like Excel does. But for compatibility, I'd still say doing it Excel way is easier than adding cell import rewrite hacks.

mahfiaz gravatar imagemahfiaz ( 2013-03-22 12:33:41 +0200 )edit

@ROSt52, you forgot what @JohnSUN pointed out, =SUMIF(A1:A5;"";B1:B5) if a cell contains ="" (a zero-length string). If you added this to column E, it would give better insight.

mahfiaz gravatar imagemahfiaz ( 2013-03-22 17:28:42 +0200 )edit

@mahfiaz - Sorry for coming back only today. Which of @JohnSUN's comments to do you mean?

ROSt52 gravatar imageROSt52 ( 2013-03-28 08:26:31 +0200 )edit

The first one as reply to my answer, few ones up. In a nutshell: being able to SUMIF if cell is zero-length string might be a feature.

mahfiaz gravatar imagemahfiaz ( 2013-03-28 16:27:10 +0200 )edit

@mahfiaz - Thanks for informaion on @JohnSUN's comment. As far as I understand it he points out the inconsistency in results I saw as well before creating the attachement here in AskLibO (= same as attachement to the bug report). IMHO there needs some dev work to be done.

ROSt52 gravatar imageROSt52 ( 2013-03-29 03:40:26 +0200 )edit

answered 2013-03-22 15:25:02 +0200

ROSt52 gravatar image

I finally could make a test and found that sumif cannot handle empty cells. The same is valid for countif.

I attached my test file. A workaround is included.

130322_SUMIF cannot handle empty cells.ods

I updated the bug report. @mahfiaz, thanks for mentioning the bug.

edit flag offensive delete link more

answered 2013-03-22 09:54:48 +0200

ROSt52 gravatar image

@stuckfly - First try the recommendation of @JohnSUN and @B.Horvat. If their formulas don't work try what I sketched below.

However, I am not sure if I understand the content of B1:B100 correct.

Looking at

it appears to me that your syntax is wrong and your formula should be:


and your formula must not be located in b1:b100. However, I could not make a test right now.

edit flag offensive delete link more

Question Tools


Asked: 2013-03-22 06:59:26 +0200

Seen: 9,569 times

Last updated: Mar 22 '13