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

Free Support Forum - aspose.com

Aspose word table cell- formula to calculate the sum in last row

Aspose.Words Product Family
#1

Dear Team,

I have created on table in word document. My requirement is to auto calculate the columns values in last row columns for all the columns in offline document. Expectation is to get updated automatically after a change in any column. Table is editable so any number of rows can be added and sum should be calculated in last row.

Currently column is updating on right click -> click “Update Fields” option. But this is not working if any column value is blank in between(Secnario2(Issue scenariao).png).

Please find the attached screenshot for reference.
Secnario1.png (22.9 KB)
Secnario2(Issue scenariao).png (25.4 KB)

Thanks in advance for your support.

Thanks
Amit Kumar Bhatt
abhatt@catina.com

#2

@abhatt The problem is that { =SUM(ABOVE) } field in Word to sum a table column, if there are any blank cells in the column, or any cells containing text, the formula only sums the cells
below the first non-numeric cell it encounters. To overcome this you need to specify exact range to sum for example { =SUM(D1:D5) } to sum cells from 1 to 5 in the 4th (D) column. In this case empty cells will be considered as zero values. But to use this approach it is required to know number of rows to sum. Unfortunately, you cannot use { =SUM(D:D) } to sum whole column because of circular reference (the formula itself is included in the total).
As a solution, you can Insert a bookmark into the main table with Table1 name, put the formula into a separate table right below the main table like this { =SUM(Table1 D:D) }. For example see the attached document. in.docx (14.3 KB)

#3

Dear Alex,

Thanks for the quick response.

As i have mentioned, my table is in MS-WORD not in excel. Kindly advise if we can implement same suggestions in word table also ? or provide any alternate solution which should work in WORD file.

Thanks
Amit Kumar Bhatt

#4

@abhatt The described solution applies to MS Word, not to Excel. Please take a look at the document I have attached.

#5

Dear Alex,

Thanks again for the quick response.

My requirement is update the value immediately in last column after changing any value in top columns. for example in below screenshot 5 should be replaced with 11. Kind of cell onchange event to update the value instantly.

image.png (26.5 KB)

Thanks
Amit Kumar Bhatt

#6

@abhatt I am afraid, there is no way to update fields immediately after the document is edited. However, you can try implementing a macro that will update fields on document save. For example see the following article: https://www.techrepublic.com/article/how-to-use-vba-to-update-fields-in-a-word-document/