Any alternative to using CountIf() in Aspose.Excel?

Aspose.Cells Product Family
1

Hi,

I am attempting to use the CountIf(). However, when opening the spreadsheet in Excel, i get a #NAME? error in the cell(s) where the formula is used. A Typical formula looks like this:

COUNTIF(D3: D3, "C") // space added after colon to keep emoticon from forming

If i place my cursor in the function editor bar and simply press return, Excel updates the function and all is well. So it is syntactically correct. How can I use the COUNTIF() function?

Also, is there anyway to use formulas that are not already coded into the Aspose object? I don't care about the Excel.CalculateFormula(). I can let Excel do the calculations when the user opens the spreadsheet...Thanks.

2

I’m having the exact same problem … very fustrating. Is there any solution to this?

Steve

3

Yes. See the thread entitled: “Unsupported Formulas” just above. Laurence compiled a new version that added support for CountIf().

4

Hi Steve,

You can download a new fix.
This fix supports countif function.

A formal release will be published in a few days and you can download it.

5

I found this thread while looking for COUNTIF, but I’m experiencing a weird problem with the hotfix above: I wonder if anyone else has experienced this.

I assigned a Formula to be “=AVERAGE(O2:O70)“

However when I open the Excel file I see the formula stored as
=AVERAGE(‘O2:O70’())

Similarly =(COUNTIF(O2:O70,”<=24”)/O77)*100 becomes
=(COUNTIF(‘O2:O70’(),"<=24")/O77)*100 (note that the O77 is not affected).

This is with aspose.excel.dll version 3.3.2.3.

6

Ah, the problem goes away with version 3.3.3.0

7

@prasenjeetd,
Aspose.Cells has replaced Aspose.Excel that is discontinued and no more available now. This new product has support for all the latest features available in different versions of MS Excel. You can use all the functions/formulas that are available in MS Excel including IFCOUNT as demonstrated in the following sample code:

Workbook workbook = new Workbook("Book1.xlsx");
workbook.Worksheets[0].Cells["F3"].Formula = "=COUNTIF(C3:C10,\">100\")";
workbook.Worksheets[0].Cells["F4"].Formula = "=COUNTIF(A3:A10,\"jim\")";
workbook.Worksheets[0].Cells["F5"].Formula = "=COUNTIF(B3:B10,\"CA\")";
workbook.CalculateFormula();
workbook.Save("Book2.xlsx");

Refer to the following article for more information about formulas:
Formulas

Give a try at this new product by downloading the latest free trial version here:
Aspose.Cells for .NET(Latest version)

You may download a runnable solution here for detailed testing of this new product.