Hi,
I have an Excel file where I need a sum per EinkBeleg. Can you help me with this?
many thanks in advance
Nina
Hi,
I have an Excel file where I need a sum per EinkBeleg. Can you help me with this?
many thanks in advance
Nina
Hi @nina.wenner
Try this
Dt.AsEnumerable().Sum(Function(row) row.Field(Of Double)("YourColumnName")).ToString
Cheers!!
DT.AsEnumerable().Sum(Function(row) row.Field(Of Double)("EinkBeleg")).ToString
Input:
Regards,
Hi @nina.wenner
=> Read Range Workbook
Output-> dt
=> Use below syntax in Assign:
dt = (From row In dt.AsEnumerable()
Group row By EinkBeleg = row.Field(Of Double)("EinkBeleg") Into Group
Let SumWertKW = Group.Sum(Function(x) Convert.ToDecimal(x.Field(Of Double)("Wert/KW")))
Let Currency = Group.First().Field(Of String)("KWähr")
Select dt.Clone.LoadDataRow(New Object() {EinkBeleg, SumWertKW, Currency}, False)).CopyToDataTable()
=> Write Range Workbook dt
back to excel.
Hope it helps!!
Hi @Parvathy,
it only works in part. As soon as the amount has desicmal digits I get an error message. i think it’s the comma. can i add a replace to this formula?
Thank you for your help.
Nina
Double.Parse(yourValue, System.Globalization.CultureInfo.CreateSpecificCulture("de-AT"))
will handle the local format. Wir haben einmal Österreich in deinem Fall angenommen.
Hi @ppr,
it still doesn’t work. The formula from parvathy only works for values without decimal places. Do you know why that is?
dt = (From row In dt.AsEnumerable()
Group row By EinkBeleg = row.Field(Of Double)(“EinkBeleg”) Into Group
Let SumWertKW = Group.Sum(Function(x) Convert.ToDecimal(x.Field(Of Double)(“Wert/KW”)))
Let Currency = Group.First().Field(Of String)(“KWähr”)
Select dt.Clone.LoadDataRow(New Object() {EinkBeleg, SumWertKW, Currency}, False)).CopyToDataTable()
Hi @nina.wenner
Using the extension Methode Field(of …) IS risky when WE cannot rely on the Data values and its formats
About the numberformats WE shared on how to Use the locals for the parsing (AT IS fine?)
So ensure that No blanks are within the Data and feel to inspect the values within one of the debugging panels
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.