# Sum Datatable

How can we sum figures in a datatable based on criteria in UiPath Studio?

Can you please show the excel data and what is the filter criteria and the desired outputâ€¦

Thanks

1 Like

In the â€śConcur Dataâ€ť sheet scroll to â€śAllocated Expense Amountâ€ť and I want to add those amounts for each person (one person can have multiple amounts) and add it to the appropriate process month sheet under total. For example â€śChua, Kheng Kokâ€ť (January) has three amounts that add up to 7087.05 and I want to add that total number to the appropriate process month table.

Thank you for your help again. If you need me to elaborate on what I meant just let me know.

I think I understand, will provide a sample workflow when I get free timeâ€¦

Thanks

what do you want the â€śPRocess monthâ€ť table to look like?
something like this?

1 Like

Heres solution (remember to change the excel path in excel application scope)
test.xaml (26.0 KB)

Data - note for Chua Kheng Kok i added 2 rows for February to show that it will sum up the totals for Jan and Feb separately

Result sheet (Jan) (highlighted total for Chua Kheng Kok in red)

Result sheet (Feb) (highlighted total for Chua Kheng Kok in red)

Result for march

Result for Jun

Steps

2. add a â€śBilling Monthâ€ť column
3. create a unique name-month table
4. for each row in unique name-month table
a) filter original table to select rows for the current employee and billing month into a temp datatable
b) use `tempDt.AsEnumerable.Sum(function(x) x.Field(Of Double)("Allocated Expense Amount"))` to calculate the sum

c) Check if sheet `"MonthlyReport - "+row("Billing Month").ToString` exists, if yes then append employee name + sum to it, otherwise use write range to automatically create a new sheet and write to itâ€¦
1 Like

Have a look at this

Monthlty Report.xaml (10.2 KB)

1 Like

Hi, hope you are having a good day

For the results on mine it duplicates, do you know by any chance why it does that?

Wait nevermind, I had to delete the results and re-run the file. Thank you so much for your help I appreciate it!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.