We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Subtotal with formula

Dear All,

Re to enclosed XLS,

I need to sum J column…J is calculated based formula (=IF(F6=0,0,(I6/F6)*100))

I am trying to run codes below, but it can not be apply if I use the above formula…

cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 2, 3, 4, 5, 6, 7, 8 });

please help


Hi Winanjaya,

Thanks for your posting and using Aspose.Cells.

You can search the cell in column I with Subtotal formula and then replace its range with column J and place the new Subtotal formula in column J cell.

Please see the following code how to accomplish this. I have attached the output xlsx file generated by this code for your reference.


string filePath = @“F:\Shak-Data-RW\Downloads\aspose+cells+pls+help.xls”;

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

//We need to find the cell with Subtotal formula in column I

string strToFind = “=SUBTOTAL(9,I”;

Cell foundCell = null;

FindOptions opts = new FindOptions();

opts.LookAtType = LookAtType.Contains;

opts.LookInType = LookInType.Formulas;

//Search the cell in column I and place subtotal in column J



foundCell = worksheet.Cells.Find(strToFind, foundCell, opts);

if (foundCell == null)


Cell targetCell = worksheet.Cells[foundCell.Row, foundCell.Column + 1];

targetCell.Formula = foundCell.Formula.Replace(“I”, “J”);

} while (true);