Sum Datatable

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

Hi @Pranav_KomandurPK

Can you please show the excel data and what is the filter criteria and the desired output…

Thanks

1 Like

2021 YTD Weekly Billing Expense Allocation.xlsx (18.2 KB)

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

  1. read range
  2. add a “Billing Month” column
    image
  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.