How to sum the specific filtered values

[bhanu.priya2 (Bhanu Priya)]

Hi Team,

Kindly find the below screenshots FYR. i am able to sum all the rows in the column but please help me to sum only 3600 rows and also need to divide the sum value in the last row of the column. Please note rows are dynamic in my excel it is not constant

Here in the above image i am able to sum the complete column just below the last row even if it is dynamic.

But here in the above image i am not able to filter and calculate only specific rows please help me

Again here in the above i need to dived the 2 sum values.

The above is the final output…pleasee help me on this…

[ashwin.ashok ((TheCodingTheory))]

Hi @bhanu.priya2 ,

If you want to sum only those rows where the Company code is equal to 3600, then could you given this a try?

Dt.AsEnumerable().Where(Function(w) w("Company Code").ToString.Trim.Equals("3600")).Sum(Function(s) Convert.ToDouble(s(1).ToString.Trim))

Kind Regards,
Ashwin A.K

[Gokul001 (Gokul Balaji)]

Hi @bhanu.priya2

Try this below expression

(From d In DtMaster.AsEnumerable Where (d("Company Code").ToString.Trim.Equals("3600")) Select v = CDbl(d(1).toString.Trim)).Sum(Function (x) x)

Regards
Gokul

[supermanPunch (Arpan)]

Hi @bhanu.priya2 ,

As an Alternate, if you want to use Excel Formula, then try the below :

=SUMIF(A:A,"3600",B:B)

[Palaniyappan (Palaniyappan P)]

Hi

Hope the below steps would help you resolve this

1. Use a assign activity like this

doub_output = Convert.ToDouble( Dt.AsEnumerable().Where(Function(a) a.Field(of String)(“Company Code”).ToString.Trim.Equals(“3600”)).Sum(Function(b) Convert.ToDouble(b(1).ToString.Trim)) ) / Convert.ToDouble(Dt.Rows(Dt.Rows.Count-1)(1).ToString)

Where doub_output is a variable of type System.Double

2. Use a write cell activity and mention the cell position where you want to type the final value and mention the input value as doub_output.ToString

Cheers @bhanu.priya2

[bhanu.priya2 (Bhanu Priya)]

@ashwin.ashok

Dt.AsEnumerable().Where(Function(w) w("Company Code").ToString.Trim.Equals("3600")).Sum(Function(s) Convert.ToDouble(s(1).ToString.Trim))

Where do i have to mention the column name of the sum which i wanted ? bcz it is giving the sum of company code column as below but i wanted sum of “Amount in profit center local curr” which is corresponding to company code where the company code is equal to 3600,

but i wanted sum of Amount in profit center local curr colum as below

[ashwin.ashok ((TheCodingTheory))]

@bhanu.priya2

Amount in profit center local curr

That will appear in the Sum Clause →

.Sum(Function(s) Convert.ToDouble(s(ColumnToSum)))

Here is the updated code->

Dt.AsEnumerable().Where(Function(w) w("Company Code").ToString.Trim.Equals("3600")).Sum(Function(s) Convert.ToDouble(s("Amount in profit center local curr").ToString.Trim))

Could you try that and let us know if it works out for you?
I can create a sequence for you if you would be so kind as to provide a sample file.

Kind Regards,
Ashwin A.K

[bhanu.priya2 (Bhanu Priya)]

Hi Palaniyappan,

Thanks for your mail

I have used a variable type as system.double only but it is throwing an above error.

[bhanu.priya2 (Bhanu Priya)]

Hi Ashwin,

Thank you for ur reply, This is working fine but how do i divide the fist sum value with the second sum value as below

[Palaniyappan (Palaniyappan P)]

@Palaniyappan

Where doub_output is a variable of type System.Double

Make sure the output variable is of type double
I think it’s string in your assign

Cheers @bhanu.priya2

[ashwin.ashok ((TheCodingTheory))]

Hi @bhanu.priya2 ,

You can either compute it in the Bot Memory and output in into Excel or grab the values from Excel Cells and then compute it within the bot memory before outputting it into Excel.

Since we have computed the Sum of company code ‘3600’ and have it handy, now I am assuming that you want to divide that by the total sum.

If that is the case, then you can do this->

Dt.AsEnumerable().Where(Function(w) w("Company Code").ToString.Trim.Equals("3600")).Sum(Function(s) Convert.ToDouble(s("Amount in profit center local curr").ToString.Trim))/Dt.AsEnumerable().Sum(Function(s) Convert.ToDouble(s("Amount in profit center local curr").ToString.Trim))

Kind Regards,
Ashwin A.K

[bhanu.priya2 (Bhanu Priya)]

No, I have used a variable type is of double only

[Palaniyappan (Palaniyappan P)]

Can you share the expression you have used

@bhanu.priya2

[bhanu.priya2 (Bhanu Priya)]

Hy Thank you so much it worked

[bhanu.priya2 (Bhanu Priya)]

I got it after changing the variable type as object… Thank you so much