How to get the sum of specific cells from other sheet

Help Activities
,
1

Hi Team,

Kindly find the below screenshot FYR, I have to get the sum of 310100 till 310190 from sheet1 and write the sum value in "Revenue from operations cell…

and I tried using below solution but it is not coming right…please help me to get the sum of specific cells

TB1.AsEnumerable().Where(Function(w) w(“Code”).ToString.Trim.Equals(“310100”,“310600”)).Sum(Function(s) Convert.ToDouble(s(“3600-SEK”).ToString.Trim))

image
image810×377 8.05 KB

image
image481×523 16.3 KB

2

Hi @bhanu.priya2 ,

Could you give this a try?

TB1.AsEnumerable().Where(Function(w) CInt(w("Code").ToString.Trim)>= 310100 AndAlso CInt(w("Code").ToString.Trim)<= 310600).Sum(Function(s) Convert.ToDouble(s("3600-SEK").ToString.Trim))

Kind Regards,
Ashwin A.K

1 Like
3

Hi @bhanu.priya2 ,

if the Range to Calculate Sum based on Code Column is from 310100 to 310190.

Then Try with the below Expression :

TB1.AsEnumerable().Where(Function(w) CInt(w("Code").ToString.Trim)>= 310100 AndAlso CInt(w("Code").ToString.Trim)<= 310190).Sum(Function(s) CDbl(s("3600-SEK").ToString.Trim))

Let us know if this gives out any issue.

4

Hi Thanks for your reply.

Can you please tell me variable type bcz it is throwing a below error.

image

5

Hi @bhanu.priya2 ,

Could you check if the variable you are assigning the values to is of type Int32 or Double?

Kind Regards,
Ashwin A.K

6

Variable type is of double and in the write line i have used tostring as below

image

7

But if I use variable type is Int32 then also it is throwing an error as option strict on disallows implicit conversions from double to integer

8

Hi @bhanu.priya2 ,

If there are any empty strings, then you can handle it using this → 

TB1.AsEnumerable().Where(Function(w) Not(IsNothing(w("Code")) OrElse String.IsNullOrEmpty(w("Code").ToString)) AndAlso CInt(w("Code").ToString.Trim)>= 310100 AndAlso CInt(w("Code").ToString.Trim)<= 310600).Sum(Function(s) Convert.ToDouble(s("3600-SEK").ToString.Trim))

Kind Regards,
Ashwin A.K

9

Hi @bhanu.priya2

Another solution

(From d In DtMaster.AsEnumerable Where Not (isNothing(d("Code")) OrElse String.IsNullorEmpty(d("Code").toString.Trim)) AndAlso CInt(d("Code").ToString.Trim)>= 310100 _AndAlso CInt(d("Code").ToString.Trim)<= 310600).Sum(Function(s) CDbl(s("3600-SEK").ToString.Trim))

Regards
Gokul

10

Hi, It worked after changing the expression from Cint to Cdbl as below

TB1.AsEnumerable().Where(Function(w) Not(IsNothing(w(“Code”)) OrElse String.IsNullOrEmpty(w(“Code”).ToString)) AndAlso Cdbl(w(“Code”).ToString.Trim)>= 310100 AndAlso Cdbl(w(“Code”).ToString.Trim)<= 310600).Sum(Function(s) Cdbl(s(“3600-SEK”).ToString.Trim))

Thank you so much

11

Hi,

Thanks for your reply it is working fine now.

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.