How can i sum total in Excel cell for row(not fixed)

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)

@Phoebe

  1. Use Excel Application Scope
  2. Read Range activity to read the excel file and create a variable for Datatable, Lets
    say dt
  3. Use Assign and write as eg : SheetIndex = dt.Rows.Count.ToString
  4. If you headers then the value should be SheetIndex = (dt.Rows.Count+2).ToString
  5. Now use Write cell activity and write as =SUM(B2 : B+SheetIndex)
  6. Again use Write cell activity and write as =SUM(C2: C+SheetIndex)
  7. 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

image

thanks for your feedback!!!
the result shows the SUM have error, did i miss something?

my dt name is “scq2”

@Phoebe

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”?
image

@Phoebe

Can you check the format of the column?
Also you can try manually by using the sum formula

Thanks

@Phoebe

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.

image

@Phoebe

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. :frowning:
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.