Sum if the cost value column larger than 0

[Mr.H]

Hi Everyone.

I have datatable as attached file.

I want to check if the “item” and the “code” columns are same and the value at the “cost” columns are negative or positive then it will Sum the cost value as below picture.

How to do that with linQ ?

Thanks in advance!

Book1.xlsx (8.2 KB)

[Priyanka_Ramesh]

Hi @Mr.H ,

Steps to be followed for the finding sum of cost of products :

1. Read the Excel
2. dtResult =dtTable.clone
3. dtResult=(From dte In dtTable.AsEnumerable Group dte By item=dte(0).ToString.Trim,code=dte(1).ToString.Trim Into Group Select dtResult.Rows.Add({item,code, Group.Sum(Function (x) CInt(x(“cost”).toString.Trim))})).CopyToDataTable

Happy automation

Thanks ,
Priyanka.

[Mr.H]

Hi Bro.

Thanks you , but it run not exactly as my condition. I only sum if the cost value is larger than 0.

As in my picture, i only want to sum the 2 row as highlighted green.

[Yoichi]

Hi,

Hope the following helps you.

dtResult = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("item").ToString,r("code").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.key.item1,g.key.item2,g.Where(Function(r) Int32.Parse(r("cost").ToString)>=0).Sum(Function(r) Int32.Parse(r("cost").ToString))},False)).CopyToDataTable

dtResult = dtResult.AsEnumerable.Concat(dt.AsEnumerable.Where(Function(r) Int32.Parse(r("cost").ToString)<=0)).CopyToDataTable

Sample20211104-2.zip (8.3 KB)

Regards,

[Mr.H]

Hi Bro.

Thanks you very much!