COUNTIF excel function works inconsistently

Aspose.Cells Product Family
1

Dear Aspose Team,

i am trying to use COUNTIF on an excelsheet:
=COUNTIF(E$6:E$13;“>0”)
this returns 1, however:
=COUNTIF(MyTable[MyColumn];“>0”)
is evalulated properly
MyColumn is a column in my table which is exactly covered by the E$6:E$13 expression.

My expectation would be that both formulas evaluated correctly.
please investigate this issue.

Best regards,
Andras

2

@careall

Summary
You are seeing different results for the two COUNTIF formulas:

=COUNTIF(E$6:E$13,">0") // returns 1
=COUNTIF(MyTable[MyColumn],">0") // returns the expected count

The structured‑reference version works, while the plain‑range version does not.

Why it happens

  1. Locale‑dependent list separator – In some regional settings Excel uses a semicolon (;) as the argument separator, in others a comma (,). If the workbook’s locale does not match the separator you use, the formula may be parsed incorrectly, leading to an unexpected result.
  2. Numbers stored as text – Cells E6:E13 might contain values that look like numbers but are actually text (e.g., because they were imported or entered with a leading apostrophe). COUNTIF treats text‑numbers differently from true numeric values, so only the cells that Excel recognises as numbers are counted.
  3. Mixed data types – A mixture of true numbers and text values in the same range can cause COUNTIF to return a count that seems too low.
  4. Structured reference handling – When you refer to a table column (MyTable[MyColumn]), Excel automatically interprets the data type of the column, which can mask the text‑number issue that appears when you use a plain range.

What to do next

  1. Check the list separator – Try both separator styles in the same workbook:
=COUNTIF(E$6:E$13,">0") // comma
=COUNTIF(E$6:E$13;">0") // semicolon

Use the one that matches your regional settings.
2. Verify the data type of each cell – In a helper column, enter:

=ISNUMBER(E6)

Copy the formula down to E13. Any FALSE indicates a text value.
3. Convert text numbers to real numbers – You can:

  • Select the range, go to Data → Text to Columns, click Finish.
  • Multiply the range by 1 (e.g., =E6*1) and copy‑paste values back.
  • Use Paste Special → Values → Multiply with the number 1.
  1. Re‑enter the formula after conversion – Once all cells are true numbers, the plain‑range COUNTIF should return the same result as the structured reference.
  2. If the issue persists – Create a minimal workbook that reproduces the problem (the table, the values in E6:E13, and both formulas) and share it with the support team for further investigation.

Following these steps should help you identify why the plain range behaves differently and bring both formulas in line.

Note: The answer is based on general Excel behaviour; no specific Aspose.Cells version details are required for this issue.

:warning: I could not fully verify this explanation against the retrieved sources. Please double-check the behaviour in your environment and confirm the exact SDK/version.

3

@careall,

Please try using our latest version/fix: Aspose.Cells v25.11 (Downloads|NuGet) if you are not already using it.

If you still find the issue with Aspose.Cells v25.11, please zip and attach the template Excel file, we will look into your issue soon.

4

@careall
By creating a sample file and testing it on the latest version v25.11 using the following sample code, we can obtain the correct results. Please refer to the attachment. result.zip (17.7 KB)

Workbook wb = new Workbook(filePath + "sample.xlsx");
Cells cells = wb.Worksheets[0].Cells;

cells["G15"].Formula = "=COUNTIF(E$6:E$13,\">0\")";
cells["K15"].Formula = "=COUNTIF(Table2[Column1], \">0\")";

wb.CalculateFormula();

Console.WriteLine(cells["G15"].StringValue);
Console.WriteLine(cells["K15"].StringValue);

wb.Save(filePath + "out_net.xlsx");

The output:

8
8

Hope helps a bit.