Getting the sum of then values in a column

[redanime94 (Red)]

I have the following excel sheet. I wanted to get the sum for the Net Amount column and then put the sum at the end of the Net Amount column.

Note, this excel spreadsheet is an output coming from a data table.

So technically what I need is to get the sum of all the values in the Net Amount before writing it into an excel spreadsheet. The values in inside the “()” are negative value, plus in some rows there are values that have “-” in them.

[raja.arslankhan (Raja Muhammad Arslan khan)]

@redanime94

1. terate through each row in the DataTable using a For Each Row activity.
2. Within the loop, use the Assign activity to assign the value of the current row and column to a variable. For example, if you want to calculate the sum of the “Amount” column, you can assign the value of the current row and column to a variable called “amount” using the following expression: amount = row("Amount").ToString().Replace(",", "")The Replace(",", "") method is used to remove the commas from the value, making it suitable for mathematical calculations.
3. Use the Add To Collection activity to add the numeric value of the “amount” variable to a List(Of Double) variable called “amountList”. This will help in storing all the numeric values for later sum calculation. Set the TypeArgument of the Add To Collection activity as Double.
4. After the loop, use the Assign activity to assign the sum of the values in the “amountList” to a variable called “totalAmount”. Use the following expression: totalAmount = amountList.Sum()The .Sum() method is used to calculate the sum of all the values in the “amountList”.

Here’s an example of how the workflow would look:

mathematicaCopy code

Assign activity:
- Variable: amountList
- Value: New List(Of Double)()

For Each Row activity:
- Input: DataTable (your DataTable variable)

   Assign activity:
   - Variable: amount
   - Value: row("Amount").ToString().Replace(",", "")

   Add To Collection activity:
   - Collection: amountList
   - Item: Double.Parse(amount)

Assign activity:
- Variable: totalAmount
- Value: amountList.Sum()

[arjunshenoy]

Hi @redanime94

Before starting the process, you need to clean up the data & you can do it by using this query in the Invoke Code:

io_dt.AsEnumerable.ToList.ForEach(Sub(row)
row("Col1") = row("Col1").ToString.Replace("(","").Replace(")","").Replace("-","").Replace(",","").Trim
End Sub)

Invoke Code Argument Panel:

Once the data cleanup is completed, the before & after data will look like this:

Then, you can cast the column into an array & get the sum directly, by using this query in the Assign activity:

sumData = dt.AsEnumerable.Select(Function(row) row("Col1").ToString).ToArray.Select(Function(x) Double.Parse(x)).Sum()

sumData variable is of a data type Double.

Once you have the sumData, you can directly write it in the intended cell.

Hope this helps,
Best Regards.

[Nikhil_Vernekar]

Use this expression to calculate the sum,

dt.AsEnumerable.Sum(Function (x) If(x(1).ToString.Replace(“(”,“-”).Replace(“)”,“”).IsNumeric, CDbl(x(1).ToString.Replace(“(”,“-”).Replace(“)”,“”)), 0))

Thanks

[Yoichi]

Hi,

Can you try the following expression?

dt.AsEnumerable.Where(Function(r) not(String.IsNullOrEmpty(r("Net amount").ToString) OrElse System.Text.RegularExpressions.Regex.IsMatch(r("Net amount").ToString,"[^\d.,\(\)]"))).Select(Function(r) if(r("Net amount").ToString.Contains("("),-1*Double.Parse(System.Text.RegularExpressions.Regex.Match(r("Net amount").ToString,"[\d.,]+").Value,System.Globalization.NumberStyles.Any),Double.Parse(r("Net amount").ToString,System.Globalization.NumberStyles.Any))).Sum()

Regards,

[AkshaySandhu (AkshaySingh Sandhu)]

Hello @redanime94
One more alternate method is below:

1. Add “Add DataRow” activity
   in ArrayRow pass this array= {"",dt_Input.AsEnumerable().Sum(Function(row) Double.Parse(row("Net amount").ToString, NumberStyles.Any) ).ToString,"","","",""}
   Please adjust the array according to your table

2. In DataTable parameter pass your table
   

   

The last row will have sum of Net amount column

Assumption: The column Net amount contains only valid numbers in format 123.00,-123.00,(123.00) etc.