SUMIF Formula with criteria in variable reference worksheet

I have the below macro that cycles through the worksheets in my workbook and adds certain values.

I have a SUMIF formula highlighted in red below.

I cannot get the criteria to work. If I hard code "Week 0" as below, this works. But I need to reference the cell which is in fact that previous cell in the sheet.

I tried

Please Login or Register  to view this content.

but this returned a #NAME? error.

I know I am close but I just cannot figure it out?

Please Login or Register  to view this content.

Please provide a sample data workbook (false data for reasons of confidentiality and privacy)

Try

Please Login or Register  to view this content.

Originally Posted by ByteMarks

Try

Please Login or Register  to view this content.

that works just great! Thank you!

How can I now autofill down for each of these? I have attached a sample workbook to illustrate what I am trying to do.

Maybe if you made the formula references absolute and got the column and the last row, you could populate the formula in one go.

Please Login or Register  to view this content.