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

Help
, ,
1

How to get a sum of all values in a column in a excel/data table without looping ?

1 Like
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

15 Likes
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.

21 Likes
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
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
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