formulas and different functions

[lchambers]

Hey there i am currently working on a spreadsheet involving a whole lot of formulas and im trying to figure out the sum of a few things. the first is the total hours spent on each task in a project (ie: painting, tear out, doors, flooring) the second is each employees total time on one whole project and the last is total time on project(all employees.

Right now i have my spreadsheet set up in sheet 1 (total hours) , week 1, week 2, week 3, and so on. on each week i have a code for each employee (ie: Joe is A, Evan is B) each task is in the vertical column and each day of the week is split between employee and time worked on that particular task. on the right hand vertical column is total hours for each task throughout the week. on the bottom of the page i have a a=joe total hours = =SUMIF(D6:P35,"*A*",E6:Q35) which is ok.

on some tasks on the same day i have joe and evan working together so employee column might say A+B and time spent is 4 hours. Which means the employees total time worked on project would be correct BUT the total man hours on the entire project would have to double those 4 hours as there was 2 people.

As of right now my total man hours right column isnt accurate, can someone help me fix this??

 

[Roger Govier]

Hi

I don't wish any offence, but your spreadsheet design is poor.
Change the layout now - it will savea lot of heartache in the future

A Week
B Employee
C Project
D Hours

Now it is easy to Summarise with formulae, or better still with a Pivot Table.

 

[iggydarsa]

I have to agree with Roger, if it is not too late i suggest you redesign the sheet.
However, if it cannot be redesigned; can't you add Joe and Evan as seperate records?
Meaning, instead of Joe + Evan 4 hours, can't you say:
Joe 2 hours
Evan 2 hours?

 

[lchambers]

no offence taken Ive quickly realized how poor it is set up. I have modeled my spreadsheet after the same timesheet that staff hands in on a project. I dont mind remodeling it as this will be very useful for all future projects but i can not seem to wrap my brain around another way of doing it. I have multiple employes and multiple days also multiple tasks i am currently trying to find a way to upload a picture as an example

 

[lchambers]

imgur: the simple image sharer

 

[iggydarsa]

You should design it as simple as Roger suggested

A Week
B Employee
C Project
D Hours

After you populate the data in a format mentioned above, you can always createa pivot table to make the data look like your existing layout.

 

[Aladin Akyurek]

imgur: the simple image sharer

Posting images or links to images are not really constructive ideas for they are not readable into Excel. By the way, it's far more attractive for a poster to craft a concise description in words followed by an example or a tiny representative sample.

 

[Roger Govier]

Hi
What I was suggesting was

A...................B........................C..........................D
Date Employee Project Hours
01 Feb 14 JIm A 6
01 Feb 14 Fred A 4
01 Feb 14 Harry B 8