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…
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…
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
- read range
- add a “Billing Month” column
- create a unique name-month table
- 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
tempDt.AsEnumerable.Sum(function(x) x.Field(Of Double)("Allocated Expense Amount"))to calculate the sum
c) Check if sheet
"MonthlyReport - "+row("Billing Month").ToStringexists, if yes then append employee name + sum to it, otherwise use write range to automatically create a new sheet and write to it…
Have a look at this
Monthlty Report.xaml (10.2 KB)
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!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.