How to sum all values in a column of an excel/Datatable?

Hi @Fer,

It is an activity comes from BalaReva.Datatable.Activities. You can know about it from video which is in the above.

Thank you
Balamurugan.S

Hi Clayton,

Quick question, is it possible to get a partial sum from the values in a DataTable column by filtering by rows?
Case in point, in the below example, i need to calculate sum in ‘Value’ column for all the rows in the ‘Type’ column that contain the string “200.”, “300.”, and so on.

I’m new to linq, managed to use your example above to return the sum of a whole column as Double, but can’t figure out how to insert the Where clause to get what i need.

Any help is highly appreciated, thank you!

image

EDIT: finally found the answer here - https://forum.uipath.com/t/real-time-business-logic-excel-summation-based-on-rows-using-identifier/56979

And so my working :smile: code looks like this:

   InputBalanceDT.Select("Type Like '200.*'").CopyToDataTable().AsEnumerable.Sum(Function(x) If(IsNumeric(x(“Value”).ToString.Trim),CDbl(x(“Value”).ToString.Trim),0))
1 Like

Hi, what I would do it is the following.

  1. Use a for each row for the datatable mentioned.
  2. Inside foreach row , put am if with following condition … row(0) contains(200.) or row(0) (contains.300)
  3. If yes total_sum = total_sum + row(1)
1 Like

This looks nice and simple and to the point, should work. Thank you @Raul8.

I took a deep dive reading up on linq and got a bit stubborn on trying to solve it that way.

I still think linq would be better as a solution because you don’t have to iterate through the whole datatable, you just query it and then apply sum. Mind you, what i posted is just some sample data, the real table will have many more rows.

Yeah for LINQ, I think you got it right.
To use Where, it’s just like the Sum:
InputBalanceDT.AsEnumerable.Where(Function(r) r("Type").ToString.StartsWith("200.") ).ToArray.Sum(Function(x) If(IsNumeric(x(“Value”).ToString.Trim),CDbl(x(“Value”).ToString.Trim),0))

4 Likes

awesome, thank you!

Hi!
I’m unable to calculate sum from a data table by using SUM function.
Tried with code
convert.ToInt32(TableName.compute(“SUM(ColumnName)”,"").ToString)

Error message
“RemoteException wrapping System.Data.DataException: Invalid usage of aggregate function Sum() and Type: Object.”

Please help me on this.


Thanks
Srideep.

Hi @Srideep,

Take a look this will help you.

Regards
Balamurugan.S

3 Likes

Does Not Work! As enumerabale is not a member of “system.Data.datattable”

1 Like

Are you on latest version of Studio and .NET Framework?
Also, please post a snippet of your code so we can make sure you have no typos. Additionally, I have found in the past, that if you delete the .AsEnumerable and retype it by hand again, it resolves the error.

1 Like

Hi ClaytonM!

I’m trying to use your code dt.AsEnumerable.Sum(Function(x) If(IsNumeric(x(“Column2”).ToString.Trim),CDbl(x(“Column2”).ToString.Trim),0))

For some reason when I use this code numbers are chanced from double to whole numbers. E.g. 123,45 is changed to 12345. If format 123.45 is is used then code is working. How can I handle this, I think its some kind of globalization issue?

This is happening because of the cultureinfo. CDbl() and other pre-built conversion methods in VB.NET assume invariant cultureinfo (which is essentially US cultureinfo). It is possible that putting in an assign activity at the top of your code assign System.Globalization.CultureInfo = new CultureInfo("es-ES") could fix the issue, but I’m not 100% sure on this. NOTE: This is changing it to spain cultureinfo as an example.

A (in my opinion) better way to do it is to update the lambda expression so it is using Double.Parse() instead of CDbl(). Double.Parse allows you to set the cultureinfo in the method directly, so you would change the code to be: dt.AsEnumerable.Sum(Function(x) If(IsNumeric(x(“Column2”).ToString.Trim),Double.Parse(x(“Column2”).ToString.Trim,CultureInfo("es-ES"),0))

Again, this is for Spain cultureinfo, you should use it to whichever culture-info is applicable to your case by changeing the “es-ES” portion within the parentheses

EDIT: Also, if you are working with monetary amounts, you should be using Decimal instead of Double. Decimal is class that was created specifically for representing monetary amounts as it does not lose any precision

2 Likes

@ClaytonM

Iv’e used this post to Sum a column in a DT, convert to result to a timespan and used it later on. But I would like to leave out specific numbers in the sum.
Column 0 Column 1 Column 2
Variable X 75,00 Min
Variable Y 30,00 Min

If I would leave out all the Y’s and perhaps other lines in the sum - how would the syntax look like?

As I see the filter syntax it’s locked to only one variable, but in my case I would like to sum several numbers and only leave out one or two variables.

Hi @Michaeljep

It sounds like you want to only Sum the values that meet a certain criteria?
Check out this previous post of mine which uses the .Where() prior to the Sum:

So you can use a condition like r("Column 0").ToString.ToUpper.Trim <> "VARIABLE Y" in the Where and it will only sum the value that are not Y. I use .ToUpper so it is not case-sensitive

dt1.AsEnumerable.Where(Function(r) r("Column 0").ToString.ToUpper.Trim <> "VARIABLE Y" ).ToArray.Sum(Function(r) If(IsNumeric(r(“Column 1”).ToString.Trim),Double.Parse(r(“Column 1”).ToString.Trim,CultureInfo("es-ES"),0))

Also refer to @Dave’s post on handling your numbers in different cultures.

Regards.

2 Likes

Hi @ClaytonM

Thanks for your time and help, I’m not complete sure I understand the line to the fullest, but it does the trick and the output is as expected.

The line below leaves out my Column “Hjælper” and only sums the rest - all good :smile:

Arbejdstid_DT.AsEnumerable.Where(Function(x) x(“column-5”).ToString.Trim <>(“Hjælper”)).Sum(Function(x) If(IsNumeric(x(“Column-6”).ToString.Trim),CDbl(x(“Column-6”).ToString.Trim),0))

Invalid usage of aggregate function Sum() and type: Object

Why dont use a template ?

Use copy to replace the template every time you need to use itand:

Exemple
Sheet1 All you values in collumn 1
Sheet2 a excel formula that will sum all values in Sheet1 and in uipath path you just need to get all the values/data to past in Sheet1 and read the cell in Sheet2 ex : A2 with formula resul its more easy.

Thank you Mr.Davey. worked for my project. :pray:

1 Like

Hello @lora
Use this below code I also facing the same issue Now I clear that.

InvoiceOP.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(“Amount”).ToString.Trim) ).ToString

image

HI, I tried to use the filter but it has an error. please assist.

dt.Compute(“SUM(Amount)”, “FBC = USD”).ToString
Assign: Cannot find column [USD].