COUNTIFS with "<>" expression and empty value returns different result in Windows and Linux, how do I fix it?

lonewbie · August 10, 2022, 5:31pm

Using:
LibreOffice Calc v7.3.2.2 on Windows 10 and
LibreOffice Calc v7.3.5.2 on Ubuntu 22.04 LTS

test scenario:
cell A1 is empty
cell B1 contains formula ="" (which results in showing as a blank cell)
cell C1 contains formula =COUNTIFS(A1:B1, “<>”)

according to LibreOffice Calc help “For <>, if the value is empty it matches non-empty cells” and I expect the result in cell C1 to be 0.

In Windows version of LibreOffice Calc this result IS 0. In Linux version it USED TO BE 0 before I upgraded to 7.3 but now it calculates to 1 - so it considers cells containing formulas as non-empty on linux regardless of the result of the formula and it considers cells containing formula that calculates to “” as empty on Windows.

So my question is:
I would like to configure my Linux LibreOffice Calc 7.3 to behave as it used to and match the behavior of my Windows LibreOffice Calc 7.3. In other words, I would like COUNTIFS in the above scenario return 0. How do I do that? I checked the Tools-Options-LibreOffice Calc settings and they are identical as far as I can tell.

And a philosophical question (this doesn’t really need answering):
Why would I care to count cells containing formulas using COUNTIFS if those cells return “” appearing as blank in the first place? Seems more of a feature for debugging spreadsheets rather than using them - it would make more sense to see cell that appears blank because formula it contains results in blank as “empty” and this is how to it used to behave on both Linux and Windows when I originally created my spreadsheets…

Thank you for taking your time to answer my question.

eeigor · August 10, 2022, 6:09pm

I remember something… Try replacing the COUNTIFS with SUMPRODUCT. It forms conditions without quotes.

mikekaganski · August 10, 2022, 6:34pm

The old (incorrect) handling of the formula cell as empty was a bug tdf#148948 fixed by @erAck in LibreOffice 7.3.4.

mikekaganski · August 10, 2022, 6:41pm

One answer to the “philosophical question” is compliance with standard.
Another is compatibility with other spreadsheet software.
And yet another is being able to distinguish (you already may match both empty cells and empty string using the empty string criterion "", so this adds flexibility).

lonewbie · August 10, 2022, 7:06pm

=COUNTIF(A1:B1,"<>"&"") or =COUNTIF(A1:B1,"<>"&" ") still return non-zero value for cells that contain formulas so this means I still can’t get a count of NON-EMPTY cells (from my perspective, considering cells with “” as a result of formula calculation).

What am I doing (and understand) wrong?

How do I get a count of empty cells in a range (including cells that contain formulas that evaluate to “”)?

Thank you for your help!

mikekaganski · August 10, 2022, 7:11pm

First of all - you used “answer”, which is for solutions for original problem. Please use comments if you want to clarify/discuss something.

Now your question is confusing. When you use “not equal to empty string”, you do not count empty things. To count empty cells and empty strings - as you requested, you use simply =COUNTIF(A1:B1; "")

lonewbie · August 10, 2022, 7:19pm

Sorry for using the wrong reply format, I’m new to this, couldn’t figure out the interface.

I don’t need the count of “empty” cells, I need a count of “non-empty” cells, considering cells that contains formulas that evaluate to “” as empty cell. I guess I can get that count by subtracting the count that you suggested from the total cell number, but that means I have to know total cell number (which I don’t, I use a range, A1:B1 is just a test case ) and makes the whole formula kinda complicated.

So let me rephrase the question - how do I count cells in the range that have any kind of VISIBLE information. I don’t want to count the cells with formulas that result in blank strings as part of that count. That bug fix really made my life miserable…

mikekaganski · August 10, 2022, 7:23pm

Excel formula: Count cells that are not blank | Exceljet explains the SUMPRODUCT trick that @eeigor mentioned.

lonewbie · August 10, 2022, 7:29pm

Ah! Got it, this works! Thank you very much.

Just a side note - this “solution” is what we used to call a hack in my previous life as a software engineer and not very intuitive, I can’t imagine how a casual user of Excel or Calc would be able to figure it out…
It seems that in a pursuit of a compliance with some poorly (IMHO) written standard we are suffering in terms of usability…

imaging replacing something like =COUNTIFS(A:A,">="&M33, A:A,"<="&M34, J:J,"=", C:C,"<>") with a SUMPRODUCT “solution”

What is more common use scenario in real life (outside of somebody trying to troubleshoot some spreadsheet) - counting all cells that appear empty as empty or count cells with formulas that result in empty cell as not-empty?