# Excel - Sum rows and check result

Hello,

I have a excel file with this kind of structure

Name Jan Fev Mar Apr May Jun Jul Aug Sep Oct Nov Dec RESULT
Emma 2 4 5 3 2 2 1 0 2 1 4 1 27
John 1 0 1 5 5 3 1 1 3 0 2 2 24
Paul 3 2 3 0 1 5 0 0 5 0 3 5 27

I would like to check if the sum of reach row per name is equal to the total in column Result
I could have several names/rows
I do a Read Range to read the dt with names and from col Jan to column Dec: dt_Annual
I create a second dt_Result with only column Result
First I need to check if the sum from Jan to Dec is what is written in col RESULT
Second if what is written in col RESULT is not correct, I need to update the cell.

I don’t know what is the most efficient way to do that.
With a LINQ query to check?
With a for each row and convert each col into Int32 and then, add each column?

Thank you

Try this in Assign activity:

dt.Columns("RESULT").Expression = "[Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec]"

Hope it helps!!

@Vandekamp
Assume the column name is ‘Result Checking’.
In your for each row of dt_Annual, use assign activity.

row(“Result Checking”) = If((CInt(row(“Jan”)) + CInt(row(“Feb”)) + CInt(row(“Mar”)) + CInt(row(“Apr”)) + CInt(row(“May”)) + CInt(row(“Jun”)) + CInt(row(“Jul”)) + CInt(row(“Aug”)) + CInt(row(“Sep”)) + CInt(row(“Oct”)) + CInt(row(“Nov”)) + CInt(row(“Dec”))) = CInt(row(“RESULT”), “Match”, “Not match”)

Then you may update the dt_Annual to your excel.

1. Read Range (Excel file) --> dt_Annual

2. Build DataTable (dt_Result)

3. For Each Row (in dt_Annual)
- Assign: sum = row.ItemArray.Skip(1).Take(12).Sum(Function(x) Convert.ToInt32(x))
- If: Convert.ToInt32(row("RESULT")) <> sum
- Assign: row("RESULT") = sum
- Add Data Row: {sum} to dt_Result

4. Write Range (Excel file) --> dt_Annual

5. If dt_Result.Rows.Count > 0
- Write Range (Excel file) --> dt_Result

Hope it helps!!

Thank you both,
I will try with a For Each Row in all the dt and Convert to Cdbl (could be decimal) with Index column, not column name, because it could change. If the sum is not equal, I update the column in an assign

CDbl(CurrentRow(13)) = Cdbl(CurrentRow(1))+Cdbl(CurrentRow(2))+Cdbl(CurrentRow(3))+Cdbl(CurrentRow(4))+Cdbl(CurrentRow(5))+Cdbl(CurrentRow(6))+Cdbl(CurrentRow(7))+Cdbl(CurrentRow(8))+Cdbl(CurrentRow(9))+Cdbl(CurrentRow(10))+Cdbl(CurrentRow(11))+Cdbl(CurrentRow(12))

→ Build Data Table

Output-> dt_Result

Output-> dt_Annual
→ Use below code in Invoke Code:

' Create new DataTable dt_Result
dt_Result= New DataTable()

' Iterate through each row in dt_Annual
For Each row As DataRow In dt_Annual.Rows
Dim sum As Integer = row.ItemArray.Skip(1).Take(12).Sum(Function(cell) If(TypeOf cell Is DBNull, 0, Convert.ToInt32(cell)))
Dim resultValue As Integer = Convert.ToInt32(row("RESULT"))
If sum <> resultValue Then