# Sum and count of Excel rows

Hi, I have some large excel file

from witch I have to select sum and count of distinct column “TEKUĆE STANJE DUGA”. I have to get this result

Could someone help me to do this?

What abot this expression

Input

Output

Code
`dtInput.AsEnumerable.Sum(Function(x) CDbl(x("Number").ToString))`

Number = YourColumnName

Thanks,
Rajkumar

I hope this is what you are looking at.

This will group the data with the column2 and get the count of each group and sum of each group

Dt - datatable in which data is present
Column2 - the column on which you want to group by
ColumntoSUM - column name of which you want the sum of

Use this in assign

``````Dt =  (From d In Dt.AsEnumerable()
Group d By k=d("Column2").toString.Trim Into grp = Group
Let coun = grp.count
Let s = grp.Sum(function(x) Cdbl(x("columntoSUM").ToString))
Let ra = New Object(){k,s,coun}

``````

Hope this helps

Cheers

I have already added it grp.Count gives the count of rows for each group

cheers

I’ve triyed, but I’m getting this error

@Olivera_Kalinic

do one thing I guess your datatable is having more columns

so create a new datatable with 3 columns and datatype string using build datatable (newdt)

and then change this in the code

1. coun = grp.Count.ToString
2. s = grp.sum(function(x) x(“column”).ToString).ToString

We are changing this because to write to same table the table already you have is having more columns than required(3).so we are creating a new datatable with 3 columns and adding data to that

cheers

This is my newDT

Table nakonFilteraDT contains just one column.

This is result after your suggestion

@Olivera_Kalinic

cheers

OK, I’ve tryed it at first, but error is at the beginning

What am I doing wrong?

@Olivera_Kalinic

As i mentioned already in row 6 in your expression editor you have to use newdt instead of nak…filter…dt

And i hope in build datatable properties you assigned output variable to newdt

Cheers

Now, I’m getting this error

@Olivera_Kalinic

Awesome you have blank values in your datatable ok

change this line

`Let s = grp.sum(function(x) If(IsNumeric(x("column").ToString),CDBL(x("column").ToString),0)).ToString`

make sure you replace the column with the appropriate column name

cheers

I just can’t find out…

@Olivera_Kalinic

Here remove the first line and in second line replace column and also after zero there are 3 brackets make it 2

cheers

I tryed everything, but without success.
This is last version, that should be working

@Olivera_Kalinic

Let me give you the full expression after compiling my side. please have a look

``````(From d In nakonfilteradt.AsEnumerable()
Group d By k=d("TEKUĆE STANJE DUGA").toString.Trim Into grp = Group
Let coun = grp.count
Let s = grp.Sum(Function(x) If(IsNumeric(x("TEKUĆE STANJE DUGA").ToString.Trim),CDbl(x("TEKUĆE STANJE DUGA").ToString.Trim),0)).ToString
Let ra = New Object(){k,s,coun}
``````

Hope with this your issue will be resolved

Note: For copy paste make sure you replace the double quotes again in the code

cheers

I really did everything you suggested, but again I’m getting the message below

@Olivera_Kalinic

PFA the project when I created it

BlankProcess5 - Copy (9).zip (2.9 KB)

cheers

Trying, but when opening project, I’m getting this error

Could you send me code trough post?

Although, I’ve tryed everything you’ve sent through your project, but the same error appears