Recent

Author Topic: COUNTIF is case sensitive in fpspreadsheet, in Excel it is not  (Read 419 times)

fred

  • Full Member
  • ***
  • Posts: 195
When opening a Excel file in spready I noticed that a cell with a COUNTIF function with text had a different count than in Excel.
It seems to be in file fpsfunc.pas procedure DoIF, DoCompareString.

People also search to have the function in Excel to behave case sensitive...
In Excel comparing strings uses a special wildcard system with the usual '?' and '*' and a '~' as an escape caharacter.

Version fpspreadsheet installed is 1.14.0.0
fpspreadsheet is a great component!
Lazarus 2.2 / FPC 3.2.0 Windows 10

wp

  • Hero Member
  • *****
  • Posts: 10858
Re: COUNTIF is case sensitive in fpspreadsheet, in Excel it is not
« Reply #1 on: May 25, 2023, 05:53:46 pm »
Fixed in the svn version on CCR.

https://exceljet.net/formulas/count-cells-equal-to-case-sensitive shows a way to extend COUNTIF to be case-sensitive. But be warned: FPSpreadsheet does not support this.

What you could do is to create an auxiliary column with the formula EXACT(cell, criterion) which performs a case-sensitive string comparison and apply COUNTIF to it:
Code: Pascal  [Select][+][-]
  1. // Example for COUNTIF function from
  2. //    https://exceljet.net/functions/countif-function
  3. //
  4. // Extended for case-sensitive comparison
  5.  
  6. program countif_casesensitive_test;
  7.  
  8. uses
  9.   fpspreadsheet, fpstypes, xlsBiff8, xlsxooxml, fpsopendocument;
  10.  
  11. const
  12.   w: array[0..5] of Integer = (8, 8, 12, 2, 22, 8);
  13.  
  14. var
  15.   workbook: TsWorkbook;
  16.   worksheet: TsWorksheet;
  17.   r, c: Cardinal;
  18.  
  19. begin
  20.   workbook := TsWorkbook.Create;
  21.   try
  22.     workbook.Options := workbook.Options + [boCalcBeforeSaving];
  23.  
  24.     worksheet := workbook.AddWorksheet('Test');
  25.  
  26.     worksheet.WriteText(0, 0, 'COUNTIF(range, criteria)');
  27.     worksheet.WriteText(2, 0, 'Name');
  28.     worksheet.WriteText(2, 1, 'State');
  29.     worksheet.WriteText(2, 2, 'Sales');
  30.  
  31.     // Text to be compared with in case-sensitive COUNTIF, in cell K1
  32.     worksheet.WriteText(2, 10, 'jim');
  33.  
  34.     worksheet.WriteText(3, 0, 'Jim');
  35.     worksheet.WriteText(3, 1, 'MN');
  36.     worksheet.WriteCurrency(3, 2, 100.0, nfCurrency, 2, '$');
  37.     worksheet.WriteFormula(3, 10, 'EXACT(A4,$K$1)');
  38.  
  39.     worksheet.WriteText(4, 0, 'Sarah');
  40.     worksheet.WriteText(4, 1, 'CA');
  41.     worksheet.WriteCurrency(4, 2, 125.0, nfCurrency, 2, '$');
  42.     worksheet.WriteFormula(4, 10, 'EXACT(A5,$K$1)');
  43.  
  44.     worksheet.WriteText(5, 0, 'Jane');
  45.     worksheet.WriteText(5, 1, 'GA');
  46.     worksheet.WriteCurrency(5, 2, 200.0, nfCurrency, 2, '$');
  47.     worksheet.WriteFormula(5, 10, 'EXACT(A6,$K$1)');
  48.  
  49.     worksheet.WriteText(6, 0, 'Steve');
  50.     worksheet.WriteText(6, 1, 'CA');
  51.     worksheet.WriteCurrency(6, 2, 50.0, nfCurrency, 2, '$');
  52.     worksheet.WriteFormula(6, 10, 'EXACT(A7,$K$1)');
  53.  
  54.     worksheet.WriteText(7, 0, 'Jim');
  55.     worksheet.WriteText(7, 1, 'WY');
  56.     worksheet.WriteCurrency(7, 2, 75.0, nfCurrency, 2, '$');
  57.     worksheet.WriteFormula(7, 10, 'EXACT(A8,$K$1)');
  58.  
  59.     worksheet.WriteText(8, 0, 'Joan');
  60.     worksheet.WriteText(8, 1, 'WA');
  61.     worksheet.WriteCurrency(8, 2, 150.0, nfCurrency, 2, '$');
  62.     worksheet.WriteFormula(8, 10, 'EXACT(A9,$K$1)');
  63.  
  64.     worksheet.WriteText(9, 0, 'Jane');
  65.     worksheet.WriteText(9, 1, 'GA');
  66.     worksheet.WriteCurrency(9, 2, 200.0, nfCurrency, 2, '$');
  67.     worksheet.WriteFormula(9, 10, 'EXACT(A10,$K$1)');
  68.  
  69.     worksheet.WriteText(10, 0, 'Jim');
  70.     worksheet.WriteText(10, 1, 'WY');
  71.     worksheet.WriteCurrency(10, 2, 50.0, nfCurrency, 2, '$');
  72.     worksheet.WriteFormula(10, 10, 'EXACT(A11,$K$1)');
  73.  
  74.     worksheet.WriteText(2, 4, 'Example');
  75.     worksheet.WriteText(2, 5, 'Result');
  76.  
  77.     worksheet.WriteText(3, 4, 'Sales over $100');
  78.     worksheet.WriteFormula(3, 5, 'COUNTIF(C4:C11,">100")');
  79.  
  80.     worksheet.WriteText(4, 4, 'Sales by ' + worksheet.ReadAsText(2,10));
  81.     // Compare the result column in which we have the results of the EXACT() function
  82.     worksheet.WriteFormula(4, 5, 'COUNTIF(K4:K11, TRUE)');
  83.  
  84.     worksheet.WriteText(5, 4, 'Sales in California');
  85.     worksheet.WriteFormula(5, 5, 'COUNTIF(B4:B11, "CA")');
  86.  
  87.     // Hide the auxiliary column
  88.     worksheet.HideCol(10);
  89.  
  90.     workbook.WriteToFile('countif_test.xls', sfExcel8, true);
  91.     workbook.WriteToFile('countif_test.xlsx', sfOOXML, true);
  92.     workbook.WriteToFile('countif_test.ods', sfOpenDocument, true);
  93.  
  94.     for r := 0 to 10 do
  95.     begin
  96.       for c := 0 to 5 do
  97.         Write(worksheet.ReadAsText(r, c):w[c]);
  98.       WriteLn;
  99.     end;
  100.     WriteLn;
  101.  
  102.   finally
  103.     workbook.Free;
  104.   end;
  105.  
  106.   Write('Press ENTER to close...');
  107.   ReadLn;
  108.  
  109. end.

fred

  • Full Member
  • ***
  • Posts: 195
Re: COUNTIF is case sensitive in fpspreadsheet, in Excel it is not
« Reply #2 on: May 29, 2023, 01:33:38 pm »
Thanks wp.

It's not a problem for me but it maken fpspreadsheet better ( at least more compatible to Excel ).
Most of the time I prefer to do the processing myself but this was some else spreadsheet.
This kind of components keeps fpc/Lazarus alive :)
Lazarus 2.2 / FPC 3.2.0 Windows 10

 

TinyPortal © 2005-2018