Sum for some rows in Excel

Help Studio
, ,
1

Hi,

I have an Excel file where I need a sum per EinkBeleg. Can you help me with this?

image

many thanks in advance
Nina

2

Hi @nina.wenner

Try this

Dt.AsEnumerable().Sum(Function(row) row.Field(Of Double)("YourColumnName")).ToString

Cheers!!

3

@nina.wenner

image
image925×534 21.6 KB

DT.AsEnumerable().Sum(Function(row) row.Field(Of Double)("EinkBeleg")).ToString

Input:

image

Regards,

4

Hi @nina.wenner

=> Read Range Workbook
image
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.
image

Hope it helps!!

5

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

image

6

Double.Parse(yourValue, System.Globalization.CultureInfo.CreateSpecificCulture("de-AT"))

will handle the local format. Wir haben einmal Österreich in deinem Fall angenommen.

7

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()

8

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

9

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