fix COUNTIFS when matching empty cells

Change-Id: I8577324c0194d594a6df0b51431729b30ac8165c
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/135047
Tested-by: Jenkins
Reviewed-by: Luboš Luňák <l.lunak@collabora.com>

2 files changed, 43 insertions, 2 deletions

diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index 9b26d2cbc4b0..14d779d2e4f3 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -307,6 +307,7 @@ public:
     void testFormulaErrorPropagation();
     void testFuncRowsHidden();
     void testFuncSUMIFS();
+    void testFuncCOUNTIFEmpty();
     void testFuncRefListArraySUBTOTAL();
     void testFuncJumpMatrixArrayIF();
     void testFuncJumpMatrixArrayOFFSET();
@@ -425,6 +426,7 @@ public:
     CPPUNIT_TEST(testFormulaErrorPropagation);
     CPPUNIT_TEST(testFuncRowsHidden);
     CPPUNIT_TEST(testFuncSUMIFS);
+    CPPUNIT_TEST(testFuncCOUNTIFEmpty);
     CPPUNIT_TEST(testFuncRefListArraySUBTOTAL);
     CPPUNIT_TEST(testFuncJumpMatrixArrayIF);
     CPPUNIT_TEST(testFuncJumpMatrixArrayOFFSET);
@@ -9316,6 +9318,44 @@ void TestFormula::testFuncSUMIFS()
     m_pDoc->DeleteTab(0);
 }
 
+// Test that COUNTIF counts properly empty cells if asked to.
+void TestFormula::testFuncCOUNTIFEmpty()
+{
+    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
+    m_pDoc->InsertTab(0, "Test");
+
+    // Data in A1:A9.
+    std::vector<std::vector<const char*>> aData = {
+        { "" },
+        { "a" },
+        { "b" },
+        { "c" },
+        { "d" },
+        { "a" },
+        { "" },
+        { "b" },
+        { "c" }
+    };
+
+    insertRangeData(m_pDoc, ScAddress(0,0,0), aData);
+
+    constexpr SCROW maxRow = 20; // so that the unittest is not slow in dbgutil builds
+    SCROW startRow = 0;
+    SCROW endRow = maxRow;
+    SCCOL startCol = 0;
+    SCCOL endCol = 0;
+    // ScSortedRangeCache would normally shrink data range to this.
+    CPPUNIT_ASSERT(m_pDoc->ShrinkToDataArea(0, startCol, startRow, endCol, endRow));
+    CPPUNIT_ASSERT_EQUAL(SCROW(8), endRow);
+
+    // But not if matching empty cells.
+    m_pDoc->SetFormula( ScAddress(10, 0, 0), "=COUNTIFS($A1:$A" + OUString::number(maxRow + 1) + "; \"\")",
+        formula::FormulaGrammar::GRAM_NATIVE_UI);
+    CPPUNIT_ASSERT_EQUAL( double(maxRow + 1 - 7), m_pDoc->GetValue(ScAddress(10, 0, 0)));
+
+    m_pDoc->DeleteTab(0);
+}
+
 // Test SUBTOTAL with reference lists in array context.
 void TestFormula::testFuncRefListArraySUBTOTAL()
 {
diff --git a/sc/source/core/tool/rangecache.cxx b/sc/source/core/tool/rangecache.cxx
index 434ba2e6de1b..79bebcfa8fe6 100644
--- a/sc/source/core/tool/rangecache.cxx
+++ b/sc/source/core/tool/rangecache.cxx
@@ -73,8 +73,9 @@ ScSortedRangeCache::ScSortedRangeCache(ScDocument* pDoc, const ScRange& rRange,
     SCROW endRow = maRange.aEnd.Row();
     SCCOL startCol = maRange.aStart.Col();
     SCCOL endCol = maRange.aEnd.Col();
-    if (!pDoc->ShrinkToDataArea(nTab, startCol, startRow, endCol, endRow))
-        return;
+    if (!item.mbMatchEmpty)
+        if (!pDoc->ShrinkToDataArea(nTab, startCol, startRow, endCol, endRow))
+            return;
 
     if (mValues == ValueType::Values)
     {