sc/qa/unit/ucalc_formula2.cxx | 30 ++++++++++++++++++++++++++++++ sc/source/core/tool/interpr1.cxx | 7 ++++--- 2 files changed, 34 insertions(+), 3 deletions(-)
New commits: commit 709bb398d0ed123e9bf52b98b46f27418bbddef5 Author: Regina Henschel <[email protected]> AuthorDate: Sun Jan 25 09:17:49 2026 +0100 Commit: Regina Henschel <[email protected]> CommitDate: Sun Jan 25 13:29:16 2026 +0100 tdf#159544 use top of parameter stack for COUNTIFS The evaluation of the fooIFS functions attempts to reduce the range to be examined so that empty cells at the end of a range are not examined. To do this, it uses the range at the bottom of the parameter stack. That works well, for example, with SUMIFS and AVERAGEIFS, because this range does not have an associated criterion, but instead contains the data to be summed up. In the case of COUNTIFS, however, all ranges have an associated criterion and such might query for empty cells. At the point in the process when the reduction takes place, the criteria are not yet known. Therefore, when the criteria are later resolved, the reduction is reversed in case the criterion queries for empty cells. The evaluation of ranges and criteria starts at the top of the parameter stack and goes then backwards. That means that the reduction was based on the range at the bottom of the stack, but the reversal of the reduction was based on the criterion at the top of the stack. The solution is to use the range of the parameter at the top of the stack to calculate the range reduction in the case of the COUNTIFS function. Change-Id: Ia451312b83ab73f52020edd204fb2e37997714e5 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/198058 Tested-by: Jenkins Reviewed-by: Regina Henschel <[email protected]> diff --git a/sc/qa/unit/ucalc_formula2.cxx b/sc/qa/unit/ucalc_formula2.cxx index 146b2b1835b4..0ec654aa9ac2 100644 --- a/sc/qa/unit/ucalc_formula2.cxx +++ b/sc/qa/unit/ucalc_formula2.cxx @@ -4729,6 +4729,36 @@ CPPUNIT_TEST_FIXTURE(TestFormula2, testHoriQueryEmptyCell) m_pDoc->DeleteTab(0); } +CPPUNIT_TEST_FIXTURE(TestFormula2, testVertQueryEmptyCell) +{ + //Test for fix for tdf#159544 + m_pDoc->InsertTab(0, u"Test"_ustr); + + // Data in A1:B10, with empty A3, A5, A6, A7, A9, A10 + m_pDoc->SetString(0, 0, 0, u"a"_ustr); // A1 col, row, tab + m_pDoc->SetString(0, 1, 0, u"b"_ustr); // A2 + m_pDoc->SetString(0, 3, 0, u"d"_ustr); // A4 + m_pDoc->SetString(0, 7, 0, u"h"_ustr); // A8 + for (SCROW nRow = 0; nRow <= 9; ++nRow) + { + m_pDoc->SetValue(1, nRow, 0, nRow + 1); + } + + m_pDoc->SetFormula(ScAddress(3, 0, 0), "=COUNTIFS(A1:A10;\"=\";B1:B10;\">0\")", + formula::FormulaGrammar::GRAM_NATIVE_UI); + // As >0 is true for all cells in B1:B10, match is determined by empty cells in col A1:A10. + // Without fix the range was reduced, so that B9 and B10 were not count and result was 4. + CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(3, 0, 0))); + + // Make sure the result is identical for exchanged queries. This tests that the range + // reduction has actually been reversed. + m_pDoc->SetFormula(ScAddress(3, 1, 0), "=COUNTIFS($B1:$B10;\">0\";A1:A10;\"=\")", + formula::FormulaGrammar::GRAM_NATIVE_UI); + CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(3, 1, 0))); + + m_pDoc->DeleteTab(0); +} + CPPUNIT_PLUGIN_IMPLEMENT(); /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx index d63b69b62a20..88a0899c3315 100644 --- a/sc/source/core/tool/interpr1.cxx +++ b/sc/source/core/tool/interpr1.cxx @@ -6080,7 +6080,9 @@ void ScInterpreter::IterateParametersIfs( double(*ResultFunc)( const sc::ParamIf } // Probe the main range token, and try if we can shrink the range without altering results. - const formula::FormulaToken* pMainRangeToken = pStack[ sp-nParamCount ]; + const bool bIsCountIfs = (nParamCount % 2) == 0; + const formula::FormulaToken* pMainRangeToken + = bIsCountIfs ? pStack[sp - 1] : pStack[sp - nParamCount]; if (pMainRangeToken->GetType() == svDoubleRef && bHasDoubleRefCriteriaRanges) { const ScComplexRefData* pRefData = pMainRangeToken->GetDoubleRef(); @@ -6305,8 +6307,7 @@ void ScInterpreter::IterateParametersIfs( double(*ResultFunc)( const sc::ParamIf // Undo bRangeReduce if asked to match empty cells for COUNTIFS (which should be rare). assert(rEntry.GetQueryItems().size() == 1); - const bool isCountIfs = (nParamCount % 2) == 0; - if(isCountIfs && (rEntry.IsQueryByEmpty() || rItem.mbMatchEmpty) && bRangeReduce) + if(bIsCountIfs && (rEntry.IsQueryByEmpty() || rItem.mbMatchEmpty) && bRangeReduce) { bRangeReduce = false; // All criteria ranges are svDoubleRef's, so only vConditions needs adjusting.
