SUMIF Error when the range changes dynamically

I have a data set that generates weekly of raw data. I am trying to use the following formula to 'clean' and organize it so others can actually understand what is going on.   This is what I am star...