How to do sum on dynamic columns in Excel

Help Studio
, ,
1

I want to download excel from outlook every month. The Excel format for this month is shown below.
image
I want to sum Jan and feb column data which is integer value and add the sum of those 2 values to Total value column.
but next time when i download excel from outlook my input file will be shown below

image
image720×172 6.42 KB

So now i need to sum mar,feb,jan column values and add sum value to Total value colums.
so every time when i download excel months will increase how to do sum

Please clarify my doubts…
Thanks in advance

2

Hi @vinjam_likitha ,

Could you give this a try?

image

(From r In Enumerable.Range(0,dt_sampleData.RowCount)
Let total = Enumerable.Range(dt_sampleData.Columns("Company").Ordinal+1,dt_sampleData.ColumnCount-4).Sum(Function(c) Convert.ToDouble(dt_sampleData.Rows(r)(c))).ToString
Let ra = dt_sampleData.Rows(r).ItemArray.Take(dt_sampleData.ColumnCount-1).Append(total).ToArray()
Select dt_result.Rows.Add(ra)).CopyToDataTable()

SumMonthItems.xaml (8.7 KB)

Kind Regards,
Ashwin A.K

3

Hi Ashwin,

Thanks for the Solution.

But this is the error i am getting ,when i opened the .xaml.

Screenshot 1
Screenshot 11324×299 191 KB

4

Hi @vinjam_likitha

Use Like this and check it

Rows.Count

Regards
Gokul

5

Thanks Gokul,

Now i am not getting the error.

6

Screenshot 3
Screenshot 31320×682 60.2 KB

Hi ,

In my input file, if there is “null” value then it showing below error.

Screenshot 2
Screenshot 21337×675 361 KB

7

HI @vinjam_likitha

Try this workflow
Sample.xaml (12.4 KB)

It works like

  • Lookup range of “Company” cell and Total Value cell addresses
  • Then write cell in the Total value as formula =SUM(Cell next to company : Cell before TotalValue)
  • Autofill Range

Regards
Sudharsan

8

Note : it will have Modern activity with it if it showed unresolved activities in your system enable show modern or show StudioX in Filter

image

Regards
Sudharsan

9

Hi Ashwin,

With your .Xaml, i got the solution without errors, but if the row values are “Null” then it is showing the above error.

@sudarshan i am trying your .xaml as well.

Thanks for the solution.

10

Hi @vinjam_likitha ,

Could you give this a try instead?
image

(From r In Enumerable.Range(0,dt_sampleData.Rows.Count)
Let total = Enumerable.Range(dt_sampleData.Columns("Company").Ordinal+1,dt_sampleData.Columns.Count-4).
Sum(Function(c) If(Double.TryParse(dt_sampleData.Rows(r)(c).ToString,0),Double.Parse(dt_sampleData.Rows(r)(c).ToString),0)).ToString
Let ra = dt_sampleData.Rows(r).ItemArray.Take(dt_sampleData.Columns.Count-1).Append(total).ToArray()
Select dt_result.Rows.Add(ra)).CopyToDataTable()

SumMonthItems_v1.xaml (9.8 KB)

Kind Regards,
Ashwin A.K

11

Hi,

Thanks Ashwin.
I got the output

Thanks Sudarshan for giving me different solution.

Thanks Gokul

1 Like
12

That’s great @vinjam_likitha

Kindly close the topic if it is solved ,So that it will be helpful for others too…

Happy Automation!

Regards
Sudharsan