Datatable - Sum multiple row/column based on the fixed header

[shubham.nagar]

Hello, I am looking for the solution as explained in the Datatable image.

1. No of Rows and Columns are all dynamic
2. I want to achieve a summation for each Entity that will be described in the First Column always.

I hope my problem statement is clear if not please let me know

[Pratik_Wavhal]

Hi @shubham.nagar

Have a look to the below thread :-

How do I find the amount?

Hi @Perawat So here is the workflow for the same :- MainPratik.xaml (11.7 KB) test.xlsx (9.6 KB) Output :- [image] I have used the Linq as below in assign activity :- This will give the count for each data for Status A :- (From d In readExcelFromSheet1.asenumerable Where d(1).ToString.Equals(row("Data").ToString.Trim) And d(2).ToString.Equals("a")).count This will give the count for each data for Status C :- (From d In readExcelFromSheet1.asenumerable Where d(1).ToString.Equals(row("Da…

Might this will give you some starter help

Mark as solution and like it if this helps you

Happy Automation

Best Regards
Er Pratik Wavhal

[ppr (Peter Preuss)]

@shubham.nagar
welcome to the forum

find some starter help here:
GroupBy_1Col_Sum_dynamicSumCols.xaml (12.1 KB)

it sums up all columns that are not used for identifying the groups (key col - like yor Company Col). It is dynamicly and if a new column will be present it is also catched for summing up

Let us know your feedback

[shubham.nagar]

Hey There, This runs ok - just one problem if any of the fields are blank it throws an error.

Scenario
(Kept Column 2 - First value as null)

Error

Coversion from String "" to type integer is not valid

[Pratik_Wavhal]

Hi @shubham.nagar

Can you Try using NOTHING keyword der

Happy Automation

Best Regards
Er Pratik Wavhal

[shubham.nagar]

Dear Pratik, this is just an example - In runtime it will be all the values will be dynamic with 100 columns and 200 rows.