Problem handling SUM with non-contiguous cells

Aspose.Cells Product Family
1

Hi,

currently I am generating SUM formulae over non-contiguous cells, namely constructs of the sort:

=SUM(A1;B2;D4;C3;O8)

and such. However the string being generated is just fine and I assign it to the Formula property. When I open the resulting Excel file, I get a formula of the kind

=SUM(‘A1;B2;D4;C3;O8’())

Note the quote and function brackets that get introduced. Maybe there’s a different way in achieving what I want, but I feel this is a bug in the formula handling or non-contiguous cell handling.

Any help would be appreciated

Regards

Kai Iske

2

Please set the formula as

=SUM(A1,B2,D4,C3,O8)

The dilimeter in formula can only be ‘,’.

3

Laurence,

thanks for the information. However Excel does support ‘;’ as a delimiter.
Anyway, maybe you should add this information to the Formula property’s help page so that people now. It only shows a sample using the range delimiter ‘:’

Regards

Kai

4

Yes. I will add it to our API reference. Thank you.

5

@Kai_Iske,
Aspose.Cells has replaced Aspose.Excel that is no more under active development now. You can perform all the operations supported by Aspose.Excel as well as work with the latest features available in different versions of MS Excel. It is observed that MS Excel supports comma ‘,’ as a separator for non-contiguous arguments in SUM function and the same is used by Aspose.Cells. You can use the SUM function with Aspose.Cells as demonstrated below:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Excel object
//int sheetIndex = workbook.Worksheets.Add();

// Obtaining the reference of the newly added worksheet by providing its sheet index
Worksheet worksheet = workbook.Worksheets[0];

// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);

// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);

// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);

// Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1,A2,A3)";

// Calculating the results of formulas
workbook.CalculateFormula();

// Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();

// Saving the Excel file
workbook.Save("output.xls");

For more information about using a formula with Aspose.Cells refer to the following article:
Formulas

The latest free trail version can be downloaded from the link given below:
Aspose.Cells for .NET (Latest Version)

Here is a comprehensive detailed solution that can be used to test the variety of features of Aspose.Cells without writing any code.