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

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

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)ā,āā)

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

@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.

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.

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.

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

sum is of type **System.Double**

Regards.

Hi @Mr_JDavey

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

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

Thanx but i have resolved it now.

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

@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.

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

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

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

Many Thanks

Alex

it is ToString()

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)

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