Thread: summarise of time sheet

Hi,

I am having a problem with a formula, I am asuming I should be using sumifs but not entirley sure on how to lay it out.

Sheet 1
in column B we have peoples names (e.g Mark)
in column C we have the Hours Worked (e.g 10:00)
in column D we have if it is Pending or complete (Pending)

Sheet 2
Column B
We have the names once and I would like for Column C to show the Hours worked

So say Mark had 14 entries in Sheet 1 I would like the formula to add together all the hours worked that have pending rather than complete.

Cheers,

Daniel

Use a PivotTable. Create the layout once and you will be able to refresh as data is added/updated. All you have to do is select your data source from sheet1 and Insert -> PivotTable (make sure you have headers). Then set up your layout using the PivotTable Field List on the right hand side. Drag "Name" into Row Labels, "Hours Worked" into Values, and "Pending/Complete" into Report Filter. You can then change your filter between Complete, Pending, or Both. As data is updated on sheet1, right click insde your pivot and select Refresh. I have also attached an example sheet. Let me know if you still want to pursue the formula route.