Sum Values Based on Criteria

[HazMattTeam]

Pretty sure the title is the right phrasing for what I am looking for.

On a worksheet I have titled "Job Numbers" I have in column c different job numbers going down by row. In column A I have bid hours for the job filled out. In column B I have used hours for the job that will need to be filled in so I can use it as comparison to make sure I am not going over my total bid hours.

On another worksheet I have titled "Week Ending 7-20-19" I have a userform to fill out job numbers that will fill in specific cells, and job hours that will fill in specific cells directly above the job number.

Here is the functionality I would need.

I would need to identify all the matching job numbers from column c of the "Job Numbers" sheet to the numbers on the "Week Ending 7-20-19" sheet, select the value in the cell(s) directly above it, sum them into column b directly next to the corresponding job number on the "Job Numbers" sheet.

Basically tallying up all the used hours for the job so I can compare them to what was bid.

The kicker is that I will be adding sheets to this workbook for each week, and would like this function to keep working on every sheet, Example "Week Ending 7-27-19", " Week Ending 8-03-19" and so on. Also the Job numbers I have in column c of the "Job Numbers" sheet may be added to in the future.... (however only job numbers will ever be in column c).


Thanks everyone!!!!!

[HazMattTeam]

Update: I have gotten this to work on a very micro level using

Please Login or Register  to view this content.

=SUMIF('WEEK ENDING 7-20-19'!J9:P9,"22463-0138",'WEEK ENDING 7-20-19'!J8:P8)+SUMIF('WEEK ENDING 7-20-19'!J10:P10,"22463-0138",'WEEK ENDING 7-20-19'!J11:P11)

Please Login or Register  to view this content.

However this is not a sustainable method as a) job sheets will be continually added, making the equation obsolete when a new sheet "WEEK ENDING 7-28-19" is added. b) there may be the potential to have 100plus employee and 3plus job numbers...... this is an extremely long method for making this work.

[sktneer]

The code below will loop through all the sheets and if the sheet name starts with "WEEK", it will sum the used hours for all the Job# in column C on Job Number Sheet and place them in corresponding rows in column B.

To test the code, click the button called "Get Used Hours" on Job Number Sheet.

Please Login or Register  to view this content.