SUMIF - Changing criteria while copying down formula

I want to repeat my SUMIF formula but have the criteria change wihtout me having to manually do it.

I have tried adding a VLOOKUP in the criteria but I either didn't do it right or it is not possible. I have attached a spreadsheet with 30 rows, but the original has over 3,000.

File A: I want the SUMIF to look at the Non-Majors worksheet for information on the Banner_ID number that is in column A for the current worksheet and return an addition of the numbers from Non-Majors worksheet. Basically, i want to be able to copy the forumla down and it change the Banner_ID number. I have the rest of it working just fine and am really hoping there is a way to copy down the formula without having to go in and change each one to the Banner_ID number in column A

File B: More robust example (still way smaller than original), but I want to be able to copy down the formula in Spent column with Excel reading the numbers from column D (and maybe E? but I will take just D if possible) while copying it down. changing the criteria automatically so i don't have to type all the numbers into the formula.

I read through a bunch of stuff and tried to find this problem already solved but couldn't and a google search showed me some interesting things to try, but nothing that would help with this issue.

Excel Forum.jpg

Try

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.