If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Timesheet calculations
I have a worksheet set up as a monthly record of hours worked:
Example Date Job No. Hours worked 01/02 2167 4 2000 2.5 2412 1.5 (total C1:C3) 8 This runs throughout the month; each day is calculated and the summary of the hours worked appears at the end, using (9,C4:Cn), e.g 160. On the next page of the same worksheet, I need the total of hours worked during the month for each job, e.g: Job No. Hours 2000 22 2167 14 2412 2 etc ... Total .....160 Is there a way to calculate/add up all the hours worked during the month for each job to enter on Page 2 rather than, as I do now, going through each day and writing down each job and the hours worked on it. |
#2
|
|||
|
|||
Timesheet calculations
SUMIF will be one way ..
Assume your source data as posted in Sheet1, where col B = job numbers, col C = hours worked Use a new/another sheet for the summary In this new/other sheet, With job numbers (eg: 2167, 2000, etc) running in A2 down, put in B2: =SUMIF(Sheet1!B:B,A2,Sheet1!C:C) Copy down to return total hours worked for the corresponding job numbers voila? hit the YES below -- Max Singapore --- "janey" wrote: I have a worksheet set up as a monthly record of hours worked: Example Date Job No. Hours worked 01/02 2167 4 2000 2.5 2412 1.5 (total C1:C3) 8 This runs throughout the month; each day is calculated and the summary of the hours worked appears at the end, using (9,C4:Cn), e.g 160. On the next page of the same worksheet, I need the total of hours worked during the month for each job, e.g: Job No. Hours 2000 22 2167 14 2412 2 etc ... Total .....160 Is there a way to calculate/add up all the hours worked during the month for each job to enter on Page 2 rather than, as I do now, going through each day and writing down each job and the hours worked on it. |
#3
|
|||
|
|||
Timesheet calculations
Thanks, Max. That works well.
However, the days of the month are actually over 4 blocks on an A4 sheet: DAY JOB NO. HRS | DAY JOB NO. HRS | DAY JOB NO. HRS |DAYJOB NO. HRS The ranges I need included in Sheet 1 are, therefore, B:B F:F J:J N:N for the Job numbers and C:C G:G K:K O:O for the hours worked. I have been trying, without success, to write a formula for this in B2 of the new sheet. Can you help, please? "Max" wrote in message ... SUMIF will be one way .. Assume your source data as posted in Sheet1, where col B = job numbers, col C = hours worked Use a new/another sheet for the summary In this new/other sheet, With job numbers (eg: 2167, 2000, etc) running in A2 down, put in B2: =SUMIF(Sheet1!B:B,A2,Sheet1!C:C) Copy down to return total hours worked for the corresponding job numbers voila? hit the YES below -- Max Singapore --- "janey" wrote: I have a worksheet set up as a monthly record of hours worked: Example Date Job No. Hours worked 01/02 2167 4 2000 2.5 2412 1.5 (total C1:C3) 8 This runs throughout the month; each day is calculated and the summary of the hours worked appears at the end, using (9,C4:Cn), e.g 160. On the next page of the same worksheet, I need the total of hours worked during the month for each job, e.g: Job No. Hours 2000 22 2167 14 2412 2 etc ... Total .....160 Is there a way to calculate/add up all the hours worked during the month for each job to enter on Page 2 rather than, as I do now, going through each day and writing down each job and the hours worked on it. |
#4
|
|||
|
|||
Timesheet calculations
You can keep it simple, use SUM(SUMIF1,...,SUMIF4)
eg in B2, all in one cell: =SUM(SUMIF(Sheet1!B:B,A2,Sheet1!C:C), SUMIF(Sheet1!F:F,A2,Sheet1!G:K), SUMIF(Sheet1!J:J,A2,Sheet1!K:K), SUMIF(Sheet1!N:N,A2,Sheet1!O:O)) Above will continue to work well even for irregular source data set-ups or should new cols ever be inserted which would disrupt current set-ups. Wave your success?, hit the YES below -- Max Singapore --- "janey" wrote: Thanks, Max. That works well. However, the days of the month are actually over 4 blocks on an A4 sheet: DAY JOB NO. HRS | DAY JOB NO. HRS | DAY JOB NO. HRS |DAYJOB NO. HRS The ranges I need included in Sheet 1 are, therefore, B:B F:F J:J N:N for the Job numbers and C:C G:G K:K O:O for the hours worked. I have been trying, without success, to write a formula for this in B2 of the new sheet. Can you help, please? |
#5
|
|||
|
|||
Timesheet calculations
Errata, typo corrected, it should be:
=SUM(SUMIF(Sheet1!B:B,A2,Sheet1!C:C), SUMIF(Sheet1!F:F,A2,Sheet1!G:G), SUMIF(Sheet1!J:J,A2,Sheet1!K:K), SUMIF(Sheet1!N:N,A2,Sheet1!O:O)) -- Max Singapore --- |
Thread Tools | |
Display Modes | |
|
|