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”);