How to Sum Negative and postive values in a single column in Excel

Hi All,

I had a two excel files, I need to sum according to the column name in which is having both Negative values and postive values. For Eample like this:
54 063,42

if have used below query using linq :dt.AsEnumerable.Sum(Function(x) If(IsNumeric(x(“Column2”).ToString.Trim),CDbl(x(“Column2”).ToString.Trim),0))

I am getting error like this:
I try to convert to int but i getting error like input string was not in correct format

please help me out for solution

Thanks in advance.


Hi @surya_teja.k

provide the sample excel file with input and output clearly mentioned.

Hi Varun,
Thanks for Replying.
Sum.xlsx (9.6 KB)

Please find the attached sheet.

I have to sum the column B in sheet 1 and column D in Sheet2.
And I have to verify sum of column B = sum of column D or Not.

Thanks in Advance,


try this

dt.AsEnumerable.Sum(Function (x) If(x(1).ToString.Replace(" ","").IsNumeric, CDbl(x(1).ToString.Replace(" ", "")), 0 ))
1 Like

Hi Varun,

Thanks a lot Varun, its worked for me.


Hi Varun,

Can we use this for Pivot Table column?

How do you want to use it in pivot table?

Hi Varn,

I was attached sheet1 right, Sheet1 was pivot table and sheet 2 was normal excel
My requirment was, i have to check sheet1 column2 sum and sheet 2 columnn10
was having same or not.

can you please help me how can i get this.

Thanks in advance,

There is no Column10 in the sheet you provided.

Thanks varun for replying, i got the solution.

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