How to sum the column values using LINQ

Help Activities
, ,
1

Hello everyone

Need to filter the “Number” column and need to sum all the column staring from A to J

Note : Can you please help with the correct format of output

Input

image
image797×237 4.16 KB

Expected Output

image

I have attached sample input and output

Sample.xlsx (9.4 KB)

@Yoichi @supermanPunch @ppr

2

Hi,

How about the following?

image
image637×526 17.2 KB 

dtResult = dt.AsEnumerable.GroupBy(Function(r) r("Number").ToString).Select(Function(g) dtResult.LoadDataRow({g.key,g.Sum(Function(r) r.ItemArray.Skip(1).Sum(Function(o) Double.Parse(o.ToString,System.Globalization.CultureInfo.CreateSpecificCulture("Es-es")))   ).ToString(System.Globalization.CultureInfo.CreateSpecificCulture("ES-es")) },False)).CopyToDataTable

Sample20221102-1.zip (9.8 KB)

BTW, result for 586 is 397435, isn’t it?

Regards,

1 Like
3

Thanks for your response @Yoichi

But, I have 25 columns in the input excel Like in the screenshot

image
image975×225 5.66 KB

In that Dt, i need to take only the Sum value

Expected Output

image

I have done my workflow like in the below

  1. Filter the data Table

  2. Sum all the column one by one

(From d in DtFilter.AsEnumerable Where Not (isNothing(d("sold_negativ")) OrElse String.IsNullorEmpty(d("sold_negativ").toString.Trim)) Select v = CDbl(d("sold_negativ").toString.Trim)).Sum(Function (x) x)

image

  1. After that i will sum all the value

  2. Used Add data Row activity

4

Hi,

For now, can you try the following? This wlll calculate from A to J in the above sheet.

dtResult = dt.AsEnumerable.GroupBy(Function(r) r("Number").ToString).Select(Function(g) dtResult.LoadDataRow({g.key,g.Sum(Function(r) r.ItemArray.Skip(3).Take(10).Sum(Function(o) Double.Parse(o.ToString,System.Globalization.CultureInfo.CreateSpecificCulture("Es-es")))   ).ToString(System.Globalization.CultureInfo.CreateSpecificCulture("ES-es")) },False)).CopyToDataTable
1 Like
6

It is possible to get the value with out round off? @Yoichi

I have Update the LINQ Like this in the expression. Could be possible to update below linq to achieve the exact out Like this Instead of 397435 to 397434,57

(From d in DtFilter.AsEnumerable Where Not (isNothing(d(5)) OrElse String.IsNullorEmpty(d(5).toString.Trim)) Select v = Double.Parse(d(5).toString.Trim,System.Globalization.CultureInfo.CreateSpecificCulture("Es-es"))).Sum(Function (x) x)
7

Hi,

The above expression doesn’t round off any value.

0.02+3.4+391571.43+5860.15 should be just 398435.

image

Can you review your calculation?

Regards,

8

Got the output correctly.

Thanks for you valuable time @Yoichi