Sum entire column in Excel

I have sheet, How to sum entire column in Excel

Sum entire Price column

image

output:

image

@priyachennuru789

Try this:


input_dt.AsEnumerable.Sum(Function(x) CDbl(x("Price").ToString.Trim))

Hi @priyachennuru789

→ Use the Read range workbook activity to read the excel and store in a datatable called inputDataTableVariable.
→ Use the assign activity to write the below LINQ Expression, Create a datatable datatype variable called Output_dt.

- Assign -> Output_dt = Input_dt.DefaultView.ToTable(False, "Category","Price")

→ Then drag and drop one more assign activity and give the below LINQ Expression,

- Assign -> Output_dt = (From row In Input_dt.AsEnumerable()
                         Group row By Category = row.Field(Of String)("Category") Into Group
                         Let PriceSum = Group.Sum(Function(x) If(TypeOf x("Price") Is String,
                                           Double.Parse(DirectCast(x("Price"), String).Replace(",", ".")),
                                           DirectCast(x("Price"), Double)))
                         Select Output_dt.LoadDataRow(New Object() {Category, PriceSum}, False)
                          ).CopyToDataTable()

→ Then use the write range workbook activity to write the outputDataTableVariable to the excel file.

Check the below workflow for better understanding,
Sequence1 1.xaml (9.0 KB)

Check the below output file,
Input 11.xlsx (9.4 KB)

Hope it helps!!

@priyachennuru789

' Arguments
' inputDataTable - Input DataTable
' outputDataTable - Output DataTable (Argument: Out)

' Ensure the outputDataTable has the correct structure
outputDataTable = New DataTable()
outputDataTable.Columns.Add("Category", GetType(String))
outputDataTable.Columns.Add("Price", GetType(Decimal))

' Perform the grouping and summing using LINQ
Dim groupedData = From row In inputDataTable.AsEnumerable()
                  Group row By category = row.Field(Of String)("Category") Into categoryGroup = Group
                  Select New With {
                      .Category = category,
                      .TotalPrice = categoryGroup.Sum(Function(r) If(IsNumeric(r.Field(Of String)("Price")), Convert.ToDecimal(r.Field(Of String)("Price")), 0))
                  }

' Populate the outputDataTable with the grouped data
For Each item In groupedData
    Dim newRow As DataRow = outputDataTable.NewRow()
    newRow("Category") = item.Category
    newRow("Price") = item.TotalPrice
    outputDataTable.Rows.Add(newRow)
Next

Put this in Invoke code

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