Sumif

[jguillen]

Hi I have this spreadsheet that has data of what our employees done for the day.
3 cell of entry per employee per day
Phase-Task- Hours
I import the iformation into access once i get this reports from the forman of all the jobs.
I need to create a summery of Hours expended by task for the whole week per report.
I have tried to use the SumIf funtion but my problem is that as you can see in the spreadsheet there are several tasks per job.
I' m kind of new to this and been serching for the answer for a few days.
Any help will be greatly appreciated





Plumber-Pipefitter Time Cards Template1.zip

[daddylonglegs]

Let's say you have a list of tasks in AF10 downwards, Try this formula in AG10

=SUM((($C$4:$AB$4="task")*( $C$5:$AB$144=AF10))*((OFFSET($C$4:$AB$4,0,1)= "hours")*OFFSET($C$5:$AB$144,0,1)))

confirmed with CTRL+SHIFT+ENTER and copied down the column.

Note: to confirm with CTRL+SHIFT+ENTER put the formula in the cell, press F2, hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } will appear around the formula in the formula bar.

Note: the data ranges shouldn't contain any text otherwise the formula will fail. G22 has a stray "/" in it. Delete this to make the formula work.....

[jguillen]

Let's say you have a list of tasks in AF10 downwards, Try this formula in AG10

=SUM((($C$4:$AB$4="task")*( $C$5:$AB$144=AF10))*((OFFSET($C$4:$AB$4,0,1)= "hours")*OFFSET($C$5:$AB$144,0,1)))

confirmed with CTRL+SHIFT+ENTER and copied down the column.

Note: to confirm with CTRL+SHIFT+ENTER put the formula in the cell, press F2, hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } will appear around the formula in the formula bar.

Note: the data ranges shouldn't contain any text otherwise the formula will fail. G22 has a stray "/" in it. Delete this to make the formula work.....

Thank You, I plugged the formula and change the reference cell to D5 witch is the column that has the task value and it works great, but it does not pick up any other task in the week that is not in the D column. Is there anyway to reference to the other tasks on the other days?. and if I may is it possible to have the task number prior to the total for that task?.

[Richard Buttrey]

Thank You, I plugged the formula and change the reference cell to D5 witch is the column that has the task value and it works great, but it does not pick up any other task in the week that is not in the D column. Is there anyway to reference to the other tasks on the other days?. and if I may is it possible to have the task number prior to the total for that task?.

Hi,

The layout of the data doesn't easily lend itself to analysis by formula. That's not to say it can't be done, but you would need to first extract a list of unique task numbers, then concatenate several SUMIF() formula together, one for each day of the week.

I found the easiest way was to number the task and hours columns 1-12 and sort the table by column rather than row, bringing all the task/hours columns together. (You need to first select all the cells and remove the merged cells option)

Then from these 6 blocks of data create a single two column list of tasks and hours by copying the 5 blocks below the first block, then the 4 blocks below the extended 1st and 2nd blocks etc.

Finally select the whole of the two column list of tasks/hours and run a Pivot Table Report.

If you do this regularly the sensible thing would be to write a macro to automate this process.

HTH

[daddylonglegs]

Here's a demo of my suggestion. I extracted the task numbers manually....

[jguillen]

Thank Again, I copy and pasted the table from access that has the tasks by job. It is cumbersome but it does for now. Again thank you so much for your help.