### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

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

#### fred

• Full Member
• Posts: 198
##### COUNTIF is case sensitive in fpspreadsheet, in Excel it is not
« on: May 25, 2023, 11:57:07 am »
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.

#### wp

• Hero Member
• Posts: 11259
##### 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.
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
98.       WriteLn;
99.     end;
100.     WriteLn;
101.
102.   finally
103.     workbook.Free;
104.   end;
105.
106.   Write('Press ENTER to close...');
108.
109. end.

#### fred

• Full Member
• Posts: 198
##### 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