Sum Datatable

[Pranav_KomandurPK (Pranav Komandur)]

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

[prasath_S]

Hi @Pranav_KomandurPK

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

Thanks

[Pranav_KomandurPK (Pranav Komandur)]

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.

[Pranav_KomandurPK (Pranav Komandur)]

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

[prasath_S]

I think I understand, will provide a sample workflow when I get free time…

Thanks

[jack.chan]

what do you want the “PRocess month” table to look like?
something like this?

[jack.chan]

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
   
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…

[kumar.varun2 (Varun Kumar)]

Have a look at this

Monthlty Report.xaml (10.2 KB)

[Pranav_KomandurPK (Pranav Komandur)]

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?

[Pranav_KomandurPK (Pranav Komandur)]

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