Free Support Forum - aspose.com

Too many parameters for function SUM with COUNTIF in excel

Hi,
We have a requirement where we have to use a formula using SUM alongwith COUNTIF functions.
Somehow there are more than 100 COUNTIF inside SUM and this is throwing “Too many parameters for function SUM”. Is there any such limitation aspose is putting in ?
Formula format is given below :
=SUM(COUNTIF1,COUNTIF2,COUNTIF3)

If yes, what is the workaround for the same?

Thanks

Hi,

If MS Excel works fine with your formula (having 100 inner COUNTIF function), then Aspose.Cells should work equally fine. Could you check in MS Excel and if your formula works, please post your template excel file here, we can check it soon.

Thank you.

Hi,
I have tried the below mentioned formula with MS excel
“=SUM(1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;100)“
It is working fine for more than 30 parameters inside SUM function.

But when I am trying to write the same formula using aspose API, it is not allowing more than 30 parameters inside SUM function and gives exception : “java.lang.IllegalArgumentException: You have entered too many parameters for fuction SUM"

Below is the sample code for generating the formula :

Workbook workbook = new Workbook();
Worksheets worksheets = workbook.getWorksheets();
Worksheet firstsheet = worksheets.getSheet(0);
Row row = firstsheet.getCells().getRow(0);
Cell cell = row.getCell(0);
StringBuffer formulaBuffer = new StringBuffer(”=SUM(”);
int lastInteger = 31;
for (int i = 1; i < lastInteger; i++) {
formulaBuffer.append(i).append(”,");
}
formulaBuffer.append(100).append(")");
cell.setFormula(formulaBuffer.toString());
try {
workbook.save(“d:\files\MyThirdBook20100324.xlsx”);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

The code works fine with value of lastInteger as ‘30’ but beyond 30 it starts failing.

Please check it.

Thanks

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for sharing the sample code.

We have found your mentioned issue after an initial test. We will look into it and get back to you soon. Your issue has been registered in our internal issue tracking system with issue id: CELLSJAVA-15331.

Thank You & Best Regards,

Hi,
By when would you be able to get back to us with patch or solution of this problem?
Thanks

Hi,

Please try the attached version, we have fixed your issue and supported the feature.

Note: It is
required to set file format to other than EXCEL97TO2003 explicitly in the code before you input more than 30 parameters for the function.

Here is my sample test code:

Workbook workbook = new Workbook();
workbook.setFileFormatType(FileFormatType.EXCEL2007);
Worksheets worksheets = workbook.getWorksheets();
Worksheet firstsheet = worksheets.getSheet(0);
Row row = firstsheet.getCells().getRow(0);
Cell cell = row.getCell(0);
StringBuffer formulaBuffer = new StringBuffer("=SUM(");
int lastInteger = 35;
for (int i = 1; i < lastInteger; i++) {
formulaBuffer.append(i).append(",");
}
formulaBuffer.append(100).append(")");
cell.setFormula(formulaBuffer.toString());

workbook.save(“d:\files\MyBook4.xlsx”,FileFormatType.EXCEL2007);



Thank you.

Hi,

By when would we be able to get a fix for 2003 format?

Thanks,

Hi,

Well, for your information, it is not an issue with Aspose.Cells for Java. Actually MS Excel (97-2003) has the limitation and you cannot put arguments more than 30 for a formula/function. For confirmation, you may try the following formula manually in MS Excel 2003:

=SUM(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,100)
so, you will get the error in MS Excel too. So, this is not possible for XLS format and you need to use XLSX (MS Excel 2007) format.


Thanks for your understanding!