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

Help
, ,
#2

Hi there @yogeshgyw,
There are a few ways to achieve this, but ‘Compute’ may be the easiest:

For example:
Assign - strSum= dtMyDataTable.Compute(“SUM(ColumnName)”, “”).ToString

The second parameter, which I’ve denoted as “”, is a filter.

Thanks,
Josh

5 Likes
#3

Hello @Mr_JDavey,
I’m getting an exception(invalid usage of aggregate function Sum() and Type:Object) if I use Compute.

my assignation is like object obj = dt.Compute(“Sum(GATENR)”,"")

#4

Hi there @yogeshgyw,
Please try the following:
dtTesting.AsEnumerable.Sum(Function (x) If(Double.TryParse(x.item("Column2").ToString, Nothing), Double.Parse(x.Item("Column2").ToString), 0))

This will convert the DT to an enumerable, then SUM the column value (as a double) if it is valid, otherwise it will simply add 0.

You will need to change the references for the DataTable (dtTesting) and columns (“Column2”).

Apologies for the delay, please let me know if it works!

Thanks,
Josh

13 Likes
Always remove DataTable rows with the same name
#5

@yogeshgyw,
Apologies, I’ve just re-read your initial post!

Please try adding ‘.ToString’ to the end of your expression and set your variable to type String.

Failing that, please try the alternative Lambda method.

3 Likes
#6

Hi JDavey,

I followed your method, but still has the problems below, saying that invalid usage of aggregate function of sum() and type:object
Appreciate your help. Thanks.
image

1 Like
#7

Hi @lora,
Use this alternate lambda expression. I’m not an expert but I was able to understand this easily and put this into Studio quickly.

image
image1054×348 30.1 KB

dt.AsEnumerable.Sum(Function(x) If(IsNumeric(x(“Column2”).ToString.Trim),CDbl(x(“Column2”).ToString.Trim),0))

sum is of type System.Double

Regards.

20 Likes
Data table Sum
Error in storing double value in excel
How do I get the total of a column in datatable?
Filter the datatable and write in second sheet
Adding Values of column
#8

Hi @Mr_JDavey

Can i add the data of two or more rows in a data table through this method?

#9

Hi there @Shaista,
Certainly, this will allow you to add all data for a specific column for each row.

More information can be found here:

Thanks,
Josh

1 Like
Data table sum into a cell
Same value of cells
#10

Thanx but i have resolved it now. :slight_smile:

1 Like
#11

hi Please visit this

#12

Hi Sara_s,
I’m not sure I am reading from the video correctly but wonder if you could confirm the command please… I read it at this:

tblaccount.Compute(“Sum(AVAIL_BALANCE)”,"").ToString

but I am getting an error: “Assign : Invalid usage of aggregate function Sum() and Type: Object.”

I cant work out if the last two characters are {} or , I don’t think it is ()

Think my eyesight is poor.

Hope you can advise
Many Thanks
Alex

1 Like
#13

@AlexRPA
If you just need to sum a column, I would suggest using the syntax like this:

tblaccount.AsEnumerable.Sum(Function(x) Convert.ToDouble(x("AVAIL_BALANCE").ToString.Trim) ).ToString

You can filter to only certain columns also using .Where, like this:

tblaccount.AsEnumerable.Where(Function(x) x("column").ToString.Trim.Equals("123456")).Sum(Function(x) Convert.ToDouble(x("AVAIL_BALANCE").ToString.Trim) ).ToString

I hope this helps.

Regards.

14 Likes
Sum of Multiple rows
#14

Brilliant, Thanks Clayton, that worked perfectly. Could I ask for another bit of advice please? Ive watched all the UIPath vid’s now but I find I am still struggling with data manipulations, is this .NET coding I need to delve deeper into on its own or something else?

Many Thanks again
Alex

1 Like
#15

UiPath works with vb.net syntax (it’s similar to C# too). Lambda and LINQ are what I find the most useful. I’m not an expert but you can look up how to do most things with Data Tables, Lists, and Arrays using these methods through online sources. The ones I use the most are Where (to filter down the data) and Select (to pull certain parts of an item).

EDIT: also, include vb.net in your online searches

3 Likes
#16

Thanks Clayton, I will start looking at these three topics in my searches.
Many Thanks
Alex

#17

please watch this

#18

it is ToString()

1 Like
#19

@AlexRPA

I’d highly recommend throwing an if statement in your function to check if the items in “avail_balance” are in fact numbers. If it isn’t a number, change it to 0.

This way if a null value or other non-number somehow finds its way into that column, it will just convert that value to 0 instead of throwing an error (assuming you don’t want an error thrown)

1 Like
#20

Good point by @Dave

Adjustment to the .Sum() would be like:

tblaccount.AsEnumerable.Sum(Function(x) If(IsNumeric(x(“AVAIL_BALANCE”).ToString.Trim), Convert.ToDouble(x(“AVAIL_BALANCE”).ToString.Trim), 0) ).ToString

so it adds 0 if the value is not numeric

6 Likes
#21

Thanks Dave and Clayton, these tips are UIPath gold!!!