COUNTIF formula with wildcard failing after Recalc method is called

Hello,

The following is a test scenario for demonstration purposes.

COUNTIF Wildcard Test.xlsx (8.9 KB)

In the uploaded "COUNTIF Wildcard Test.xlsx" file I have the following formulas:

  • Cell B1: =COUNTIF(A:A, "")*
  • Cell B2: =COUNTIF(A:A, "<>")

image

image

This will work in a way that if any text is added to any cell on Column A, both B1 and B2 will start counting.

The issue I am having is that after adding text to Column A, whenever the Recalc() method is called the COUNTIF function on cell B1 will contain the number "1048576" like if all the cells on the column were filled with text data, but the cell B2 with the other COUNTIF function will contain the number "1" as expected.

The following code snipped can be used to replicate the issue:

private void RecalcTest(XlsFile xls)
{
	var xf = 0;
	var a1 = xls.GetCellValue(1, 1, 1, ref xf);
	var b1 = xls.GetCellValue(1, 1, 2, ref xf);
	var b2 = xls.GetCellValue(1, 2, 2, ref xf);

	Console.WriteLine($"A1: {a1}"); // null
	Console.WriteLine($"B1: {b1}"); // 0
	Console.WriteLine($"B2: {b2}"); // 0

	xls.SetCellValue(1, 1, 1, "Test", xf);
	xls.Recalc();

	/*    
	// Since saving the file also calls the Recalc() method, the same result will be obtained
	var byteArray = xls.ToArray();
	using var stream2 = new MemoryStream { Position = 0 };
	xls.Save(stream2);
	*/

	a1 = xls.GetCellValue(1, 1, 1, ref xf);
	b1 = xls.GetCellValue(1, 1, 2, ref xf);
	b2 = xls.GetCellValue(1, 2, 2, ref xf);

	Console.WriteLine($"A1: {a1}"); // Test
	Console.WriteLine($"B1: {b1}"); // 1048576
	Console.WriteLine($"B2: {b2}"); // 1
}

This is working correctly if I modify the excel file using Microsoft Excel, as seen in the following image.

image

Am I doing something wrong?

I am using .NET FlexCel version 7.15.0

Thanks in advance for the help.

Hi,
You are right, the logic is considering that all empty cells match the "*" condition, but Excel considers they don't.
We will update the recalc engine so this case is handled the same as Excel