How to Sum all the values in multiple column index using LINQ

[Sidney_Vogel]

Hi all, I would like some help.

I have a data table with multiple columns, I would like to get the sum of each column but only the las 5 columns, using index instead of column names, this is because the sheet has repeating column names, how can I achieve this with LinQ?

Thank you in advance for all the help.

For example: reportData.AsEnumerable().Sum(Function(row) row.Field(Of Integer)(-1) where -1 is the last column hoe ever this is not working for me.

[ABHIMANYU_THITE1 (ABHIMANYU THITE)]

@Sidney_Vogel

LinQ

LINQ Tutorials

Tutorials for LINQ Beginners 1. LINQ In General UiPath | LINQ Training | Learn LINQ for UiPath from A to Z | LINQ Query Structure | LINQ Query Parts - YouTube 2. LINQ Group Function UiPath | LINQ Group Function | Get Unique Rows from DataTable | Duplicate Rows | Not Duplicate Rows - YouTube 3. LINQ Join Function UiPath | LINQ Join Function | Inner Join | Left Outer Join | Right Outer Join | Full Outer Join - YouTube 4. LINQ Let Function UiPath | LINQ Let Function | Calculate varia…

@Sidney_Vogel - Checkout the tutorials, This Might be helpful for you

[Yoichi]

Hi,

only the las 5 columns

How about the following?

dt.AsEnumerable.Sum(Function(r) r.ItemArray.Skip(dt.Columns.Count-5).Sum(Function(o) Double.Parse(o.ToString)))

Sample20221118-2aL.zip (2.8 KB)

Regards,

[Sidney_Vogel]

@Yoichi

dt.AsEnumerable.Sum(Function(r) r.ItemArray.Skip(dt.Columns.Count-5).Sum(Function(o) Double.Parse(o.ToString))

Hi @Yoichi thank you for the info, however the columns must be calculated individually

I also forgot to mention that the last row/cell from the column should not be taken into consideration when adding all the values, for example, consider the following column:
0.00
2849.00
309.00
418.00
1559.00
55680.00
170.00
128.00
893.00
24.00
3941.00
20615.00
68996.00
22554.00
420.00
540.00
10.00
98.00
104.00
16.00
64.00
558.00
174.00
2.00
30.00
15.00
210.00
0.00
224.00
0.00
888.00
0.00
40.00
3.00
2.00
1.00
1.00
91.00
236.00
649.00
6.00
1.00
30.00
817.00
7542.00
3985.00
6074.00
25.00
61.00
42.00
76641.00
17.00
34.00
210.00
1.00
868.00
1344.00
49.00
3989.00
33913.00
776.00
633.00
4893.00
4697.00
329160 This cell/row should not be included in the sum

[Sidney_Vogel]

Hi @ABHIMANYU_THITE1 thank you for this valuable information, how ever my requirements are a bit more specific.

[Yoichi]

Hi,

@Sidney_Vogel

the columns must be calculated individually

I also forgot to mention that the last row/cell from the column should not be taken into consideration when adding all the values, for example, consider the following column:

The following expression will work. Can you check the following sample, too?

arrResult = Enumerable.Range(dt.Columns.Count-5,5).Select(Function(i) dt.AsEnumerable.Take(dt.Rows.Count-1).Sum(Function(r) Double.Parse(r(i).ToString))).ToArray()

arrResult is Double array.

Sample20221118-2aLv2.zip (3.0 KB)

Regards,

[Sidney_Vogel]

@Yoichi

Enumerable.Range(dt.Columns.Count-5,5).Select(Function(i) dt.AsEnumerable.Take(dt.Rows.Count-1).Sum(Function(r) Double.Parse(r(i).ToString))).ToArray()

@Yoichi you are a genius, this worked perfectly, thank you so much for this, this is great.

Is there a way to only get the value, instead of an array? I guess could add (indexnumber) at the end of the array, like this: Enumerable.Range(dt.Columns.Count-5,5).Select(Function(i) dt.AsEnumerable.Take(dt.Rows.Count-1).Sum(Function(r) Double.Parse(r(i).ToString))).ToArray()(1)
But is there another way to get just the individual sum of the column and not an array?

[Yoichi]

Hi,

If you need sum value of specific column, the following help you.

dt.AsEnumerable.Take(dt.Rows.Count-1).Sum(Function(r) Double.Parse(r(4).ToString))

The above returns sum value of 4th column of the datatable (0-base index)

Regards,

[Sidney_Vogel]

@Yoichi

dt.AsEnumerable.Take(dt.Rows.Count-1).Sum(Function(r) Double.Parse(r(4).ToString))

@Yoichi Thank you very much, this is what i was looking for, very much appreciated.