Calculate sum of entire column & write at bottom

Help Activities
, ,
1

i want to autosum entire column of excel & write the sum value at bottom of that column in UiPath
but column position changes everytime

2

@Anil_G @sangeethaneelavannan1 @Rahul_Unnikrishnan @supermanPunch @Yoichi

3

@pravin_bindage

Read the dt and gice only start range as A1 so it will read data from whole sheet

Then dt.AsEnumerable.Sum(function(x) if(isnumeric(cdbl(x("column").tostring), cdbl(x("column").tostring,0))) will give sum

And dt.rowcount will give thhe count…add 2 to get the last row and use write cell

Cheers

1 Like
4

Hi @pravin_bindage

  1. Use the Read Column activity to read the values in the column that you want to sum. This will return a list of strings, which you can store in a variable.
  2. Use a For Each loop to iterate through the list of strings, and use the Int32.Parse or Double.Parse method to convert each string to a numeric value.
  3. Inside the loop, use a variable to keep track of the running total, and add the value of each string to the running total as you iterate through the list.
  4. After the loop has completed, you can use the Write Cell activity to write the sum value to the cell at the bottom of the column. Make sure to specify the correct cell address, and set the value of the cell to the sum value stored in your running total variable.
  5. If the column position changes every time, you can use the Find Column activity to search for the column with a specific header or name, and then use the column index returned by this activity to specify the correct column in the Read Column and Write Cell activities.

I hope this helps! Let me know if you have any questions.

Thanks!!!

1 Like
5

Hi @pravin_bindage

You can try with LINQ expression to Sum the column Values

(From d in DtBuild.AsEnumerable Where Not (isNothing(d("Column Name")) OrElse String.IsNullorEmpty(d("Column Name").toString.Trim)) Select v = CDbl(d("Column Name").toString.Trim)).Sum(Function (x) x)

In the Write cell activity pass this as a Range Need to update the column index A, B , C .....

"A"+(DtBuild.Rows.Count+2).ToString

Check out the XAML file

SumUsingLINQ.xaml (8.4 KB)

image
image767×388 14.2 KB

Regards
Gokul

1 Like
6

thanks to all for your response
My problem is column position is dynamic ,otherwise currently i’m calculating the sum & writing at bottom using VBA code

7

Hi @pravin_bindage

You can use Look Range activity to find the column position.

8

If anyone know how to handle this the pls help me.
I’m stuck at this point.

9

ok i will try this & get back