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

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 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=""))}

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

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

Sort by » oldest newest most voted 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:

=SUMIF(B1:B100;"<>";A1:A100)
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, 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)

@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.

@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 - Sorry for coming back only today. Which of @JohnSUN's comments to do you mean?

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

more @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.

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

=sumif(B1:B100,"",A1:100)

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

more

Stats

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

Seen: 9,569 times

Last updated: Mar 22 '13