SUMIF doesn't consider only first column

[Pekshev]

Read and complete the full issue template

Do not randomly delete sections. They are here for a reason.

Do you want to request a feature or report a bug?

• Bug
• Feature
• Question

Did you test against the latest CI build?

• Yes
• No

If you answered No, please test with the latest development build first.

Version of ClosedXML

0.100.3
(Installed via nuget.org using NuGet package manager)

What is the current behavior?

When reading a complex file with lots of formulas and links, it gives wrong formula calculation results

What is the expected behavior or new feature?

Complete this.

Is this a regression from the previous version?

No

Reproducibility

This is an important section. Read it carefully. Failure to do so will cause a 'RTFM' comment.

No special actions are needed: just open the attached file and take the value of cell (13, 9) from the first sheet as an example

Value in file:

The value produced by the code:

Code to reproduce problem:

using (var fs = new FileStream(@"E:\Temp\Расчёт гидравлики СНТ Вишня.xlsx", FileMode.Open, FileAccess.Read))
{
    var workbook = new XLWorkbook(fs);
    var worksheet = workbook.Worksheet(1);
    var xlCell = worksheet.Cell(13, 9);
    xlCell.InvalidateFormula();
    MessageBox.Show(xlCell.Value.ToString());
}

• I attached a sample spreadsheet. (You can drag files on to this issue)
  Расчёт гидравлики СНТ Вишня.xlsx

[jahav]

Please provide a smaller sample, minimal would be best.

The workbook has 35 worksheets with a large amount of data and formulas are across many worksheets, in an alphabet I am not familiar with (i.e. checking where references go across worksheets is an excercise - does this looks similar). Also, my Excel will say that it needs to repair the file on load, likely because I use English version.

[Pekshev]

That's the problem - I can't reproduce this problem myself. A user sent me this file and it is exactly the problem.
For the sake of experiment I tried to read it through EPPlus and it managed, giving the correct result.
Then I tried to recalculate all formulas on all sheets and got the error about cyclic dependency. I'll take another look at this file as much as I can

[jahav]

I have trimmed it down a lot, but it is still massive.
The cell I13 uses INDEX function and indexes from two MATCH functions. I have separated it to individual cells (A1, B1 and C1). The indexes are correctly evaluated, but the INDEX function doesn't return the correct value.

Trimmed file (still massive): default7.xlsx

[jahav]

@Pekshev Please track the call to a function/feature that doesn't work (i.e. go deeper and deeper into the call chain of the workbook and verify that ClosedXML gives the different result than Excel for the arguments/cell). Minimum verifiable example is a requirement for bugs.

I am closing the issue in the meantime. When you do that, reopen the issue.

[igitur]

I managed to trace the cause. On a different sheet, there is a SUMIF function that is passed a matrix range, not array. Excel considers only the first column, but ClosedXML was treating it differently.

[igitur]

Example of culprit: in the original attached workbook.
'Расчёт гидравл.'!K120 has the formula: =SUMIF($C65:I$119,"суммарный*",K$65:K119). Notice that the first parameter is a multi-dimensional range (matrix).