How to filter the data and add them

Book1.xlsx (8.5 KB)
Here is the sample file,How to add the data only the dept IT and age should be 21.How to add that data.



If you want only the filtered value then use a assign activity like this

dt = dt.AsEnumerable().Where(Function(a) a(“dept”).ToString.Contains(“IT”) AND a(“age”).ToString.Contains(“21”)).CopyToDatatable()

This gives the filtered datatable

Now to get the sum

Use another assign activity like this

Int_sum = dt.AsEnumerable().Sum(Function(row) cint(row(“salary”)))

Where int_sum is a variable of type int32

To have them in one expression then use a assign activity

int_sum = dt.AsEnumerable().Where(Function(a) a(“dept”).ToString.Contains(“IT”) AND a(“age”).ToString.Contains(“21”)).Sum(Function(row) CInt(row(“salary”).ToString))

Hope this helps

Cheers @anjani_priya



for getting sum use the below expression

sumtotal=dt1.AsEnumerable.Where(Function(x) x(3).ToString.trim.Equals(“IT”) and cint(x(1).ToString.trim)=21).Sum(Function(a) cint(a(2).ToString))

here sumtotal is the int32 datatype

to get the datatable after filtering, use the below expression

dt1.AsEnumerable.Where(Function(x) x(3).ToString.trim.Equals(“IT”) and cint(x(1).ToString.trim)=21).copytodatatable

hope this helps


please use this to get the required sum

sum = dt.AsEnumerable.Where(function(x) x("dept").ToString.Equals("IT") And x("age").ToString.Equals("21")).Sum(function(x) CDBL(x("salary").ToString))

here sum is a variable is of type double

Hope this helps


Can’t I use pivot table? and do this process.If yes,how to do.Send the sample code?


May I know why do you need a pivot?

if you need to do as pivot please use create pivot table activity and pass the rows and columns as you need


We can directly do with a simple linq like this


The value should be dynamically to give that.
If we have 100 rows but we need only the sum of salary whose dept is IT.How to sum them?


can you explain what was your scenario

i think you no need to create pivot


ya it is possible

try with the expression given

or refer this (4.5 KB)


the above formula will give sum of any number of rows…

if you want to pass variables in place of age and department name can use them as well

sum = dt.AsEnumerable.Where(function(x) x("dept").ToString.Equals(var_Department) And x("age").ToString.Equals(var_Age)).Sum(function(x) CDBL(x("salary").ToString))

var_Department,var_Age are two string type variables


Book1.xlsx (11.0 KB)

requirement is I want sum of salary of every dept separately


dt1.AsEnumerable.Where(Function(x) x(“dept”).ToString.trim.Equals(“IT”) and cint(x(“age”).ToString.trim)=21).copytodatatable

gives only the rows which are having Dept has IT

or you want all the salary of the dept=IT in to an array

arr_salary=dt1.AsEnumerable.where(Function(x) x(3).ToString.trim.Equals(“IT”) And CInt(x(1).ToString.trim)=21).Select(Function(x) x(“Salary”).tostring).toarray

here arr_salary is array of string

to get all the salaries in a single line use below expression


The age changes dynamically
if we done pivot the the values will be filtered
the filtered values should be in sheet2 how to do that


dt2=dt1.AsEnumerable.Where(Function(x) x(“dept”).ToString.trim.Equals(“IT”)).copytodatatable

use write range workbook activity to write the datatable into sheet2


Then you can use this

Create a datatable with two columns 1 columns is department a string type and othe column total of doubel type

Now use the following

Newdt = (From d In DT.AsEnumerable Group d By k=d("dept").toString.Trim Into grp = Group Let c = grp.Sum(function(x) CDBL(x("salary").ToString)) Let ra = New Object(){k,c} Select r = Newdt.Rows.Add(ra)).CopyToDataTable

This will give you a datatable with eqch department name and the salary sum

If you want sum of each department and age together then in build include one more column age

Newdt = (From d In DT.AsEnumerable Group d By k=d("dept").toString.Trim,k1=d("age").ToString Into grp = Group Let c = grp.Sum(function(x) CDBL(x("salary").ToString)) Let ra = New Object(){k,k1,c} Select r = Newdt.Rows.Add(ra)).CopyToDataTable


Book1.xlsx (11.0 KB)
From sheet1 all the data should be of salary should be add dept wise and should fill in sheet2
how to do it
send the sample code?


Use the above code in assign and use write range


can you send the sample program i cant understand the code

I have used pivot table and I did sum of all the data dept wise how to paste that data in sheet2