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

thanks


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.

C#


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


do

{

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


if (foundCell == null)

break;


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

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


} while (true);



workbook.Save(“output.xlsx”);