Looking for a formula to "SUM" by different job numbers

cuzar

New Member
 A B C D E F G H I J Date Time In Job Number Lunch In Lunch Out Time Out Total Work Hours Total Site Hours Pieces Vacation 8:00 AAA111 12:00 12:30 15:00 6.50 7 100 8 8:00 BBB111 12:00 12:30 16:00 7.50 8 150 8:00 AAA111 12:00 12:30 16:00 7.50 8 75 6 8:00 BBB111 12:00 12:30 15:00 6.50 7 125

<tbody>
</tbody>
This a sample, I'm looking for a formula for the table below that will pull data based on the Job Number in column "C" above and put that Job # in col "B". I need to total for AAA111 and BBB111 or any other job # to show up in col/s G,H,I,J as they are below. I have around 30 different job numbers that may show up. I'm using a Data validation list for the job number on a seperate tab that I call my control tab. Any help would be greatly appreciated, Cuzar
 A B C D E F G H I J JOB# AAA111 TOTALS: 14 15 175 14 BBB111 14 15 275

<tbody>
</tbody>

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
 Date Time In Job Number Lunch In Lunch Out Time Out Total Work Hours Total Site Hours Pieces Vacation 8:00 AAA111 12:00 12:30 15:00 6.5 7 100 8 8:00 BBB111 12:00 12:30 16:00 7.5 8 150 8:00 AAA111 12:00 12:30 16:00 7.5 8 75 6 8:00 BBB111 12:00 12:30 15:00 6.5 7 125 AAA111 14 15 175 14 BBB111 14 15 275 0

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=8></COLGROUP>
G7 copy down and accross:
SUMIF(\$C\$2:\$C\$5,\$B7,G\$2:G\$5)

Robert, Thank you very much, Your formula works. Is there a formula for B7 that would find and paste the Job number/s from the upper field into the lower field so I don't have to manually type the job number AAA111 into B7. I will have up to 30 or so possible job number that people can input into the field above, they will be using a data validation field from my control tab. Thank you very much, Cuzar

So do you need to Extrach Unique values for your data validation?

The Data Validation is for the many people filling in the time sheet, to have a given set of job numbers to pick from for column "C" at the top. Each time sheet has 39 rows. The lower part of the time sheet has 7 rows for the different job numbers that are inputted in the upper field, and the totals that your formula provides. Your formula works great for the lower right hand corner of the time sheet, Columns G - J. The lower left corner Column A has the text Job # in it, column "B" next to that job # is where a formula would be great to pick out the different job numbers that have been manually selected in the field at the top of the time sheet in column "C". I have taken a picture of the time sheet how can I get it so you could take a look at it? Thank you for your help, Cuzar

Replies
1
Views
81
Replies
1
Views
151
Replies
5
Views
174
Replies
2
Views
143
Replies
5
Views
228

1,207,012
Messages
6,076,148
Members
446,187
Latest member
LMill

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back