Bug. MATCH and SUMIFS does not work correctly with text lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- veb86 · 2024-07-11 12:05 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     b.Options := [boReadFormulas];    b.ReadFromFile('d:\test1\4.xlsx', sfOOXML);    sh := b.GetFirstWorksheet;     sh.CopyCell(1,5,2,5,b.GetFirstWorksheet);    sh.CopyCell(2,5,3,5,b.GetFirstWorksheet);    sh.CopyCell(3,5,4,5,b.GetFirstWorksheet);    sh.CopyCell(1,8,2,8,b.GetFirstWorksheet);    sh.CopyCell(2,8,3,8,b.GetFirstWorksheet);    sh.CopyCell(3,8,4,8,b.GetFirstWorksheet);     b.CalcFormulas;    b.WriteToFile('d:\test1\444111111.xlsx', sfOOXML, true); ---------------------------------------------------------------------- veb86 · 2024-07-11 12:14 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 · 2024-07-12 10:40 The updated ccr version yields the same results as when I copy the cells directly in Excel. ---------------------------------------------------------------------- wp · 2024-07-12 16:53 Quote from: veb86 on July 11, 2024, 02:14:48 pmNow 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 · 2024-07-15 06:00 Quote from: wp on July 12, 2024, 06:53:17 pmYou 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 Quote from: wp on July 12, 2024, 12:40:23 pmThe 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_typeBehavior1 or omittedMATCH 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.0MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.-1MATCH 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 · 2024-07-15 10:01 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 · 2024-07-15 10:08 Quote from: wp on July 15, 2024, 12:01:55 pmUnless 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 · 2024-07-15 10:40 Quote from: veb86 on July 15, 2024, 12:08:30 pmI don't see the attached file. You forgot to add it Sorry, attached to previous post now. ---------------------------------------------------------------------- veb86 · 2024-07-15 10:55 Quote from: wp on July 15, 2024, 12:01:55 pmUnless 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 · 2024-07-15 13:07 Try commit r9384 ---------------------------------------------------------------------- veb86 · 2024-07-16 05:25 Quote from: wp on July 15, 2024, 03:07:01 pmTry commit r9384 Everything is working now. I only checked the case when the 3rd argument is 0 ---------------------------------------------------------------------- veb86 · 2024-07-16 05:28 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 · 2024-07-16 06:17 Please look at the bug with SUMIFS. I don't understand his reasons ---------------------------------------------------------------------- wp · 2024-07-17 09:00 Quote from: veb86 on July 16, 2024, 07:28:19 amSUMIFS 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 nodes you see the results calculated by fpspreadsheet for the formulas in the preceeding lines - it's 2-1-2-1: Code: XML  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ...              SUMIFS($J$2:$J$5,$D$2:$D$5,D2)        2      ...              SUMIFS($J$2:$J$5,$D$2:$D$5,D3)        1       ...            SUMIFS($J$2:$J$5,$D$2:$D$5,D4)      2       ...              SUMIFS($J$2:$J$5,$D$2:$D$5,D5)        1      ... ---------------------------------------------------------------------- veb86 · 2024-07-17 09:14 Quote from: wp on July 17, 2024, 11:00:13 amCannot reproduce this. Just rechecked. There is an error. Resubmitting the test file Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     b.Options := [boReadFormulas];    b.ReadFromFile('d:\test1\4.xlsx', sfOOXML);    sh := b.GetFirstWorksheet;            sh.CopyCell(1,8,2,8,b.GetFirstWorksheet);    sh.CopyCell(2,8,3,8,b.GetFirstWorksheet);    sh.CopyCell(3,8,4,8,b.GetFirstWorksheet);    b.CalcFormulas;      b.WriteToFile('d:\test1\444111111.xlsx', sfOOXML, true); Saved the following: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SUMIFS($J$2:$J$5,$D$2:$D$5,D2)0SUMIFS($J$2:$J$5,$D$2:$D$5,D3)0SUMIFS($J$2:$J$5,$D$2:$D$5,D4)0 ---------------------------------------------------------------------- wp · 2024-07-17 09:42 Still cannot reproduce. Isn't this file the same? What is different? ---------------------------------------------------------------------- veb86 · 2024-07-17 10:20 The same file. Check, did you commit everything? I don't understand why the bug is not reproducible. ---------------------------------------------------------------------- wp · 2024-07-17 10:48 There is one uncommitted experimental change in my local repo related to circular reference formulas. I think it has no effect on this issue, but I committed it now for you. ---------------------------------------------------------------------- veb86 · 2024-07-17 11:05 Quote from: wp on July 17, 2024, 12:48:31 pmThere is one uncommitted experimental change in my local repo related to recursive formulas. I think it has no effect on this issue, but I committed it now for you. Now it has stopped compiling, now I'll try to update the compiler. I updated it a year ago ---------------------------------------------------------------------- veb86 · 2024-07-17 12:12 Compilation completed successfully. But the SUMIFS formula works with an error(((( Please look at the project in the archive and the picture. I don't understand how this can be ---------------------------------------------------------------------- wp · 2024-07-17 15:51 Quote from: veb86 on July 17, 2024, 02:12:20 pmBut the SUMIFS formula works with an error(((( Please always specify what the error is. It would save me a lot of time if I knew what to look for. Maybe, in this case you refer to the four COPY.Values which have the same cell value (2) - this is because you have the workbook.CalcFormulas afterwards, i.e. the cell values simply have not yet been calculated. Or do you refer to the four CALC.Values which you see as zero - on my system they are listed correctly like in Excel as 2-1-2-1. Strange... What is your operating system? Your Lazarus/FPC version? IDE is 32bit or 64bit? ---------------------------------------------------------------------- veb86 · 2024-07-17 17:09 Quote from: wp on July 17, 2024, 05:51:09 pm Or do you refer to the four CALC.Values which you see as zero - on my system they are listed correctly like in Excel as 2-1-2-1. Strange... What is your operating system? Your Lazarus/FPC version? IDE is 32bit or 64bit? I show that the copying is all correct.  Error after calculation.  I get 0 in all cells, but should get 2-1-2-1.  My operating system is Windows 10. Lazarus is a trunk (v3.99).  FPC - trunk.  Today I updated to the latest version.  IDE - 64 bit ---------------------------------------------------------------------- wp · 2024-07-17 17:49 Quote from: veb86 on July 17, 2024, 07:09:55 pmMy operating system is Windows 10. Lazarus is a trunk (v3.99).  FPC - trunk.  Today I updated to the latest version.  IDE - 64 bit Mine: Win 11, Laz/main (3.99), FPC 3.22, 32-bit IDE, but during these tests I also switched to the 64-bit IDE with the same constellation, to Laz/main + FPC/main (32-bit again), and to Linux Mint with Laz/main/FPC 3.2.2 64bit IDE - same observation: no 0-0-0-0, but 2-1-2-1. Very strange... ---------------------------------------------------------------------- veb86 · 2024-07-17 19:43 I asked my programming mentor to run an example.  It has the same values ​​after calculation 0-0-0-0. I appeal to the community.  Maybe someone who uses this library can take a look at it.  What values ​​were obtained when performing the calculation? ---------------------------------------------------------------------- wp · 2024-07-17 21:18 Simplify the file: Replace the "copy" cells by the formulas directly, in order to exclude bugs in the copy instructions. Replace the calculations of column D by their result directly ("A-B", etc). In the end, there should be a file with the SUMIFS formulas only. If that still produces the 0-0-0-0 result, check function CompareStringWithWildcards in unit fpsfunc: the last instruction, before "end" must be "Result := true" - otherwise you don't have the current version. The final option is to use the debugger to trace the instructions in TsFuncComparer.Execute (unit fpsfunc): Essentially here you have three loops, the outer one ("for r") runs over the rows of the range, the next one ("for c") runs over the columns of the range (but we have only one column), and the inner loop ("while matches...") runs over the criteria in the formula arguments. The variable "matches" is true initially, and with every cycle through the "while" loop the current argument set is compared to the test value by "CompareCell()". Once a mismatch is found "matches" becomes false and this cell does not contribute to the sum. But when all criteria are met "matches" still is true, and the current cell value of the r/c loop is added to the sum. When SUMIFS results in 0, TsFuncComparer either is not entered at all or left very early, or the loops are not executed, or CompareCell results in false in every comparison. In the latter case, step into CompareCell and check the intermediate variables. ---------------------------------------------------------------------- veb86 · 2024-07-18 05:41 I updated the library today and everything worked! I didn't change anything! Thank you! ---------------------------------------------------------------------- veb86 · 2024-07-22 05:43 Hello. Please tell me what was the reason. Why was my calculation wrong, but yours was correct?