hi everyone,
please help to assist how to sum total in each columns cell ? (for row 618)
(EX:B618=SUM(B2:B617) ,but next day data position will be different, not fixed position)
- Use Excel Application Scope
- Read Range activity to read the excel file and create a variable for Datatable, Lets
say dt - Use Assign and write as eg : SheetIndex = dt.Rows.Count.ToString
- If you headers then the value should be SheetIndex = (dt.Rows.Count+2).ToString
- Now use Write cell activity and write as =SUM(B2 : B+SheetIndex)
- Again use Write cell activity and write as =SUM(C2: C+SheetIndex)
- Now use Auto Fill Range and define your range
The SheetIndex will count the rows of the datable, so even it will work even the count changes
Hope this may help you
Thanks
thanks for your feedback!!!
the result shows the SUM have error, did i miss something?
in Write cell write as below
“=SUM(F3:F”+rows+“)”
rows is a variable so you should not but in double quotes
Hope this may help you
Thanks
HI @Srini84
it works!!! thank you very much,
but there are one last issue, why excel can’t see the real total but “0”?
Can you check the format of the column?
Also you can try manually by using the sum formula
Thanks
Also you can try as below
once the read range is places use assign activity and write as below in assign
ColumnSum = dts.AsEnumerable.Sum(Function (row) CDbl(row.Field(Of String)(“ColumnName”))).ToString()
So ColumnSum is a string variable where it store the value of the sum and later you can write to the datatable and use write range activity to write the sum
Hope this may help you
Thanks
the columns is general format and i use sum formula by manual that is correct result.
Then can you check the difference of the both in the formula you wrote?
Try to write the same manual formula for the G column also
If not working can you share a sample excel file?
Thanks
I am sorry, new user can’t upload attachment for now.
both formula looks the same, i don’t know how figure out that…
thank you so much anyway!!!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.