Bug. MATCH and SUMIFS does not work correctly with text

[veb86]

The search for matches in the text is performed incorrectly, resulting in errors.
I think the situation is approximately the same for MATCH and SUMIFS
You need to watch this.
my code:

Code: Pascal  [Select][+][-]

1.     b.Options := [boReadFormulas];
2.     b.ReadFromFile('d:\test1\4.xlsx', sfOOXML);
3.     sh := b.GetFirstWorksheet;
4.     sh.CopyCell(1,5,2,5,b.GetFirstWorksheet);
5.     sh.CopyCell(2,5,3,5,b.GetFirstWorksheet);
6.     sh.CopyCell(3,5,4,5,b.GetFirstWorksheet);
7.     sh.CopyCell(1,8,2,8,b.GetFirstWorksheet);
8.     sh.CopyCell(2,8,3,8,b.GetFirstWorksheet);
9.     sh.CopyCell(3,8,4,8,b.GetFirstWorksheet);
10.     b.CalcFormulas;
11.     b.WriteToFile('d:\test1\444111111.xlsx', sfOOXML, true);
12.  

[veb86]

Now I noticed a problem in the operation of the column formula :'(
Try to calculate the page and look at the value of the cells (0,1)(0,2)(0,3)

Download this file, it takes into account MATCH and SUMIFS

[wp]

The updated ccr version yields the same results as when I copy the cells directly in Excel.

[wp]

Now I noticed a problem in the operation of the column formula :'(
Try to calculate the page and look at the value of the cells (0,1)(0,2)(0,3)

You mean the circular reference? This is because you have COL(A1) in cell A1. I know in Excel this works, but in fpspreadsheet it does not. Use COL(A2) instead.

[veb86]

You mean the circular reference? This is because you have COL(A1) in cell A1. I know in Excel this works, but in fpspreadsheet it does not. Use COL(A2) instead.

Understood. Calculations corrected, everything worked

The updated ccr version yields the same results as when I copy the cells directly in Excel.

MATCH

MATCH is not working properly.
The formula does not correctly accept the 3rd argument.

Match_type	Behavior
1 or omitted	MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0	MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1	MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a

3rd argument is 0, exact FIRST match in range

Match now returns the following:
Cell(F2) = 1
Cell(F3) = 2
Cell(F4) = 3
Cell(F5) = 4

Match should be:
Cell(F2) = 1
Cell(F3) = 2
Cell(F4) = 1
Cell(F5) = 4

SUMIFS

SUMIFS in my example returns the value 0, although it should return the value:
Cell(I2) = 2
Cell(I3) = 1
Cell(I4) = 2
Cell(I5) = 1
I think that the search for the value is not happening correctly

[wp]

Unless you are talking of something else I cannot reproduce this. See attachment. File 41.xlsx is your initial file, file 41-wp was written by fpspreadsheet, file 41-excel was written by Excel 2016 after manually performing the copy operations mentioned in the project code. In both files 41-wp and 41-excel the result cells display the same content.

[veb86]

Unless you are talking of something else I cannot reproduce this. See attachment. File 41.xlsx is your initial file, file 41-wp was written by fpspreadsheet, file 41-excel was written by Excel 2016 after manually performing the copy operations mentioned in the project code. In both files 41-wp and 41-excel the result cells display the same content.

I don't see the attached file. You forgot to add it

[wp]

I don't see the attached file. You forgot to add it

Sorry, attached to previous post now.

[veb86]

Unless you are talking of something else I cannot reproduce this. See attachment. File 41.xlsx is your initial file, file 41-wp was written by fpspreadsheet, file 41-excel was written by Excel 2016 after manually performing the copy operations mentioned in the project code. In both files 41-wp and 41-excel the result cells display the same content.

Excel 2019: file 41-wp
Watch a video.
When I click calculation, the value in cell F4 changes from 3 to 1. The correct value is 1

[wp]

Try commit r9384

[veb86]

Try commit r9384

Everything is working now. I only checked the case when the 3rd argument is 0

[veb86]

SUMIFS BUG
SUMIFS now returns the following:
Cell(I2) = 0
Cell(I3) = 0
Cell(I4) = 0
Cell(I5) = 0

SUMIFS should be:
Cell(I2) = 2
Cell(I3) = 1
Cell(I4) = 2
Cell(I5) = 1

I think that the search for the value is not happening correctly

[veb86]

Please look at the bug with SUMIFS. I don't understand his reasons

[wp]

SUMIFS BUG
SUMIFS now returns the following:
Cell(I2) = 0
Cell(I3) = 0
Cell(I4) = 0
Cell(I5) = 0

SUMIFS should be:
Cell(I2) = 2
Cell(I3) = 1
Cell(I4) = 2
Cell(I5) = 1

I think that the search for the value is not happening correctly

Cannot reproduce this. Here is an excerpt from the xlsx file written; in the <v> nodes you see the results calculated by fpspreadsheet for the formulas in the preceeding lines - it's 2-1-2-1:

Code: XML  [Select][+][-]

1. ...
2.       <c r="I2" s="0">
3.         <f>SUMIFS($J$2:$J$5,$D$2:$D$5,D2)</f>
4.         <v>2</v>
5.       </c>
6. ...
7.       <c r="I3" s="0">
8.         <f>SUMIFS($J$2:$J$5,$D$2:$D$5,D3)</f>
9.         <v>1</v>
10.       </c>  
11. ...
12.       <c r="I4" s="0">
13.         <f>SUMIFS($J$2:$J$5,$D$2:$D$5,D4)</f>
14.         <v>2</v>
15.       </c>  
16. ...
17.       <c r="I5" s="0">
18.         <f>SUMIFS($J$2:$J$5,$D$2:$D$5,D5)</f>
19.         <v>1</v>
20.       </c>
21. ...

[veb86]

Cannot reproduce this.

Just rechecked. There is an error.
Resubmitting the test file

Code: Pascal  [Select][+][-]

1.     b.Options := [boReadFormulas];
2.     b.ReadFromFile('d:\test1\4.xlsx', sfOOXML);
3.     sh := b.GetFirstWorksheet;        
4.     sh.CopyCell(1,8,2,8,b.GetFirstWorksheet);
5.     sh.CopyCell(2,8,3,8,b.GetFirstWorksheet);
6.     sh.CopyCell(3,8,4,8,b.GetFirstWorksheet);
7.     b.CalcFormulas;  
8.     b.WriteToFile('d:\test1\444111111.xlsx', sfOOXML, true);
9.  

Saved the following:

Code: Pascal  [Select][+][-]

1. <c r="I2" s="0"><f>SUMIFS($J$2:$J$5,$D$2:$D$5,D2)</f><v>0</v></c>
2. <c r="I3" s="0"><f>SUMIFS($J$2:$J$5,$D$2:$D$5,D3)</f><v>0</v></c>
3. <c r="I4" s="0"><f>SUMIFS($J$2:$J$5,$D$2:$D$5,D4)</f><v>0</v></c>