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

Hello @balupad14, by using your approach I get this error:

Capture

Hi @Fer,

I have tested your scenario. Yes I am getting the same error. Why ? Because when you read the data from excel to datatable everything is string. So here you are trying to do the sum with string column. That is the reason you are getting this error. If the column is integer , you won’t get this error. you have 2 solution for this.

Solution 1 (More work.)

  1. You have to convert the column as integer
  2. Or else add new column (tempVal) with datatype as integer.and convert all values to integer to this column(tempVal)

Solution 2 (Single Drag and drop.)

  1. Use the sum activity from “datatable extension” bellow video attached.

You can get it from UiPath Go also.

Sample Project : Fer.zip (7.9 KB) (The sample studio version is 2018.4.0)

Regards
Balamurugan.S

1 Like

@balupad14,

Can you send me a printscreen of what is presented in the red area?

Anyway I am doing the sums as follows:

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