Excel opreation

[Mayur_Pawar]

what if i want to do addition of cell values which i had passed in read range or write range activity .how can i do that ?
like
Column A
21
23
21
34
54
153 so finally i want addition of this value using read range or write rang activity

thanks

[Palaniyappan (Forum Leader)]

@Mayur_Pawar

153 so finally i want addition of this value using read range or write rang activity

Yah thats possible with READ RANGE activity
like get the datatable as output and then we can use a assign activity like this
out_value = dt.AsEnumerable.Sum(Function(x) If(IsNumeric(x(“ColumnName”).ToString.Trim),CDbl(x(“ColumnName”).ToString.Trim),0))

where out_value is of type System.Double

Cheers @Mayur_Pawar

[Mayur_Pawar]

thanks @Palaniyappan
Can you elaborate this out_value = dt.AsEnumerable.Sum(Function(x) If(IsNumeric(x(“ColumnName”).ToString.Trim),CDbl(x(“ColumnName”).ToString.Trim),0))
like which variable works what ?

cheers @Palaniyappan

[ImPratham45 (Prathamesh Patil)]

Read Range Output : DT1
Assign : Count=DT1.Rows.count+2 And Sum=0
Use For Each Row
Sum=Sum+Cint(Row(“Column A”).tostring)
After For Each Row
Write Cell
Cell: “A”+Count.Tostring (Note: A is just a Ref put columnA Cell Name)
Value: Sum.Tostring

[Palaniyappan (Forum Leader)]

@Mayur_Pawar

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

lets go one by one
dt is your datatable variable
where dt.AsEnumerable will segregate the datatable to datarows
then SUM is a Aggregate Function
which will sum all the values in the specified column in this expression
Function(x) If(IsNumeric(x(“ColumnName”).ToString.Trim),CDbl(x(“ColumnName”).ToString.Trim),0)

where this function does a validation like only if the value in each cell of that column along each row is NUMERIC then it will be considered for SUMMATION else taken as 0

Cheers @Mayur_Pawar

[Mayur_Pawar]

I’m getting this error
thanks

[Palaniyappan (Forum Leader)]

we need to make a validation here
like this
Sum=Sum+ If(IsNumeric(row(“ColumnName”).ToString.Trim),CDbl(row(“ColumnName”).ToString.Trim),0)

Cheers @Mayur_Pawar

[Mayur_Pawar]

hey @Palaniyappan
you said SUM is aggregation function so it should come automatically or should i need to define ?

[Palaniyappan (Forum Leader)]

But here its a variable right not called from METHOD ASENUMERABLE, thats why buddy
here sum is called as a function

@Mayur_Pawar

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

but here in this its been used as a variable

@Mayur_Pawar

[indra]

@Mayur_Pawar Corret me if i am wrong you are trying to do sum of column A value right?

[ImPratham45 (Prathamesh Patil)]

Then Before Sum
Try If Condition
If
Row(“Column A”).tostring.trim=“”
Then leave
Else
Sum=Sum+Cint(Row(“Column A”).tostring)

If you having double values then istead Cint use Cdbl

[Mayur_Pawar]

Yes @indra

[indra]

@Mayur_Pawar Here you go with xaml try this and let me know

Sum-Column.zip (15.4 KB)

[Mayur_Pawar]

getting this error …
@indra

[indra]

@Mayur_Pawar Open mainSequence xaml in notepad and add
System.Data.DataSetExtensions

Refer this

[Mayur_Pawar]

@indra

System.Data.DataSetExtensions

why is it like this ?

[indra]

@Mayur_Pawar Simply open the mainSequence xaml in Notepad and create new line with this String:

<AssemblyReference>System.Data.DataSetExtensions</AssemblyReference>

[Mayur_Pawar]

@indra

System.Data.DataSetExtensions

Yeah i understand but im asking why is it like that ? the reason behind making this change ?

[Mayur_Pawar]

i tried but not working

[samir (Samir Mohite)]

Hi @Mayur_Pawar,

Try following code,

sum1 (Double) = (DT.AsEnumerable().Sum(Function(i) Convert.ToDouble(i("Column A").ToString)))