# 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?

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

@JohnSUN, I agree. :)