Excel opreation

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

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

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

1 Like

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

1 Like

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

1 Like


I’m getting this error
thanks

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

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

1 Like

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

but here in this its been used as a variable

@Mayur_Pawar

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

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

Yes @indra

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

Sum-Column.zip (15.4 KB)


getting this error …
@indra

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

Refer this

why is it like this ?

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

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

image

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

i tried but not working

Hi @Mayur_Pawar,

Try following code,

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