SUM VALUE OF COLUMN

[nidhi.kowalli]

How do I get the sum of particular column based on condition of other column.

Example,
Col1 - Amount
Col2- Description
Col3- Header (consists of values 1 and 2)

I need to calculate the SUM of “Amount” for “Header =2”

[Gokul001 (Gokul Balaji)]

Hi @nidhi.kowalli

Try the below expression

You can simply store this in a dataTable and then using For Each Row activity than using assign DT.AsEnumerable.Sum(Function(a)Convert.ToDouble(a(“Amount”).ToString))

[Sudharsan_Ka]

Hi @nidhi.kowalli

Can you elaborate your issue little bit more?

Regards
Sudharsan

[nidhi.kowalli]

SURE,
Following are 3 columns in my sheet.
Col1 - Amount
Col2- Description
Col3- Header (consists of values ‘1’ and ‘2’)

I need to calculate the sum of AMOUNT (col1), for Header =2

Hope this is clear!

[Sudharsan_Ka]

You can give condition like

For Each row
If Header =2
Calculate the Amount

Have you tried this way?

Regards
Sudharsan

[ppr (Peter)]

give a try on following:

Assign Activity
Left: mySum | DataType Double
Right

(From d in YourDataTableVar.AsEnumerable
Where d(“Col3”).toString.Trim.Equals(“2”)
Select v= CDbl(d(“Col1”).toString.Trim)).Sum(Function (x) x)

Also have a check if DataTable.Compute Method would help

 sumObject = YourDataTableVar.Compute("Sum(Col1)", "Col3 = '2' ")

[nidhi.kowalli]

@Gokul001

DT.AsEnumerable.Sum(Function(a)Convert.ToDouble(a(“Amount”).ToString))

M storing Amount value in Variable , as STRING!
This is the issue!!

[nidhi.kowalli]

@ppr
M Sorry,
I really did not understand your solution and how to use!!

please elaborate.

[ppr (Peter)]

as mentioned, use it within an assign acitvity

[nidhi.kowalli]

@ppr

(From d in YourDataTableVar.AsEnumerable
Where d(“Col3”).toString.Trim.Equals(“2”)
Select v= CDbl(d(“Col1”).toString.Trim)).Sum(Function (x) x)

This is working only for sum value=0
Its throwing error If sum value is Non zero (Positive and Negative)

Error:

"Assign: Conversion from string “” to type ‘Double’ is not valid "

[ppr (Peter)]

the problem is that you have empty values within the amount column

give a try on following

(From d in YourDataTableVar.AsEnumerable
Where d(“Col3”).toString.Trim.Equals(“2”)
Where Not (isNothing(d(“Col1”)) OrElse String.IsNullOrEmpty(d(“Col1”).toString.Trim))
Select v= CDbl(d(“Col1”).toString.Trim)).Sum(Function (x) x)

feel free to share the sample data with us

[nidhi.kowalli]

@ppr

Thank you!
That worked!

You were right, there was an empty cell due to which I had got error!

Cheers!