excel - Sumif across two worksheets

Question

I am trying to add up various staff members costs on individual projects (contained in a separate worksheet called "Timesheet Entry") via a sumif (i.e. if the project number in the left hand column matches the timesheet entry then add up all of my staff costs for it, then do the next project, and so on.

The code is almost working! but returning 0 as the sum against each project.

With Worksheets("Cost Data Summary")

    Lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
   
        For i = 2 To Lastrow
        .Cells(i, 2).Value = Application.SumIf(Worksheets("Timesheet Entry").Range("A2:F50"), "=.Cells.Value(i, 2)", Worksheets("Timesheet Entry").Range("F2:F50"))
        
        Next i

End With

I can only assume that I am somehow referencing the criteria incorrectly. I want it to reference the text in the cell to the left of where the sumif answer goes.

Any help would be very much appreciated.

question from:https://stackoverflow.com/questions/65557856/sumif-across-two-worksheets

Answer 1

Try replacing

"=.Cells.Value(i, 2)"

with

.Cells.Value(i, 2)

When enclosed in quotes, the code turns into a string literal. You shouldn't need the = sign for the argument.