COUNTIF is case sensitive in fpspreadsheet, in Excel it is not lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- fred · 2023-05-25 09:57 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! ---------------------------------------------------------------------- wp · 2023-05-25 15:53 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i // Example for COUNTIF function from//    https://exceljet.net/functions/countif- function//// Extended for case-sensitive comparison program countif_casesensitive_test; uses  fpspreadsheet, fpstypes, xlsBiff8, xlsxooxml, fpsopendocument; const  w: array[0..5] of Integer = (8, 8, 12, 2, 22, 8); var  workbook: TsWorkbook;  worksheet: TsWorksheet;  r, c: Cardinal; begin  workbook := TsWorkbook.Create;  try    workbook.Options := workbook.Options + [boCalcBeforeSaving];     worksheet := workbook.AddWorksheet('Test');     worksheet.WriteText(0, 0, 'COUNTIF(range, criteria)');    worksheet.WriteText(2, 0, 'Name');    worksheet.WriteText(2, 1, 'State');    worksheet.WriteText(2, 2, 'Sales');     // Text to be compared with in case-sensitive COUNTIF, in cell K1    worksheet.WriteText(2, 10, 'jim');     worksheet.WriteText(3, 0, 'Jim');    worksheet.WriteText(3, 1, 'MN');    worksheet.WriteCurrency(3, 2, 100.0, nfCurrency, 2, '$');    worksheet.WriteFormula(3, 10, 'EXACT(A4,$K$1)');     worksheet.WriteText(4, 0, 'Sarah');    worksheet.WriteText(4, 1, 'CA');    worksheet.WriteCurrency(4, 2, 125.0, nfCurrency, 2, '$');  worksheet.WriteFormula(4, 10, 'EXACT(A5,$K$1)');     worksheet.WriteText(5, 0, 'Jane');    worksheet.WriteText(5, 1, 'GA');    worksheet.WriteCurrency(5, 2, 200.0, nfCurrency, 2, '$');  worksheet.WriteFormula(5, 10, 'EXACT(A6,$K$1)');     worksheet.WriteText(6, 0, 'Steve');    worksheet.WriteText(6, 1, 'CA');    worksheet.WriteCurrency(6, 2, 50.0, nfCurrency, 2, '$');    worksheet.WriteFormula(6, 10, 'EXACT(A7,$K$1)');     worksheet.WriteText(7, 0, 'Jim');    worksheet.WriteText(7, 1, 'WY');    worksheet.WriteCurrency(7, 2, 75.0, nfCurrency, 2, '$');    worksheet.WriteFormula(7, 10, 'EXACT(A8,$K$1)');     worksheet.WriteText(8, 0, 'Joan');    worksheet.WriteText(8, 1, 'WA');    worksheet.WriteCurrency(8, 2, 150.0, nfCurrency, 2, '$');  worksheet.WriteFormula(8, 10, 'EXACT(A9,$K$1)');     worksheet.WriteText(9, 0, 'Jane');    worksheet.WriteText(9, 1, 'GA');    worksheet.WriteCurrency(9, 2, 200.0, nfCurrency, 2, '$');  worksheet.WriteFormula(9, 10, 'EXACT(A10,$K$1)');     worksheet.WriteText(10, 0, 'Jim');    worksheet.WriteText(10, 1, 'WY');    worksheet.WriteCurrency(10, 2, 50.0, nfCurrency, 2, '$');  worksheet.WriteFormula(10, 10, 'EXACT(A11,$K$1)');     worksheet.WriteText(2, 4, 'Example');    worksheet.WriteText(2, 5, 'Result');     worksheet.WriteText(3, 4, 'Sales over $100');    worksheet.WriteFormula(3, 5, 'COUNTIF(C4:C11,">100")');     worksheet.WriteText(4, 4, 'Sales by ' + worksheet.ReadAsText(2,10));  // Compare the result column in which we have the results of the EXACT() function    worksheet.WriteFormula(4, 5, 'COUNTIF(K4:K11, TRUE)');     worksheet.WriteText(5, 4, 'Sales in California');    worksheet.WriteFormula(5, 5, 'COUNTIF(B4:B11, "CA")');     // Hide the auxiliary column    worksheet.HideCol(10);     workbook.WriteToFile('countif_test.xls', sfExcel8, true);    workbook.WriteToFile('countif_test.xlsx', sfOOXML, true);    workbook.WriteToFile('countif_test.ods', sfOpenDocument, true);     for r := 0 to 10 do    begin      for c := 0 to 5 do        Write(worksheet.ReadAsText(r, c):w[c]);      WriteLn;    end;    WriteLn;   finally    workbook.Free;  end;   Write('Press ENTER to close...');  ReadLn; end. ---------------------------------------------------------------------- fred · 2023-05-29 11:33 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