Calculation including table data, text and numbers

Hi,

I have a sheet where i'm assigning jobs to workers. I have the following

- In B5:B14 - Job list
- In F5:F14 - The expected hours to be taken for each of the above jobs.
- In H5:J14 - I have created a list of all workers initials that may attend site. I want to select the person who has been assigned the work in one of three boxes on this row. There are three boxes as some tasks take up to 3 people to complete. Sometimes only 1 person will complete a task.

and

- In M8:M18 - A pivot table with a list of workers full names on site for that day
- In N8:N18 - Within the same pivot table above, the hours that each worker is on site for each day
- In O8:O18 - I want to put the hours remaining for each engineer after they have been assigned the day's work. This is the calculation i'm struggling with, which i'm hoping to do based on the above mentioned fields.

Is it possible to subtract hours of work to be assigned from an workers time on site with this method?

To add something further to the mix; on some days there might only be 5 jobs on the list, on others there might be 15. Also, on some days there might only be 2 people on site, on others, there might be 6 or 7.

Many thanks for your help in advance.

Hi and welcome,
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi,

Thanks for coming back to me.

Please see attached the example as requested.

Cheers

Hi, is anyone able to assist with this at all?

Many thanks

Originally Posted by JeteMc

Hello jackjstokes and Welcome to Excel Forum.
For column G try: =PRODUCT(F5,COUNTA(H5:J5))
As to the "Hours remaining without work" see if the following helps.
1. Populate an "Assigned Hours" column (O) using: =SUMPRODUCT((H$5:J$19=M6)*(F$5:F$19))
2. Use the following to populate the "Hours remaining..." column: =N6-O6
Note that the formula in column O may be moved and/or the column may be hidden for aesthetic purposes.
Let us know if you have any questions.

JeteMc - Thank you! This is great and was exactly what i was looking for. I really appreciate your help.

Have a beer on me!

Problem SOLVED