Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tracking billable hours
Hi,
I have a spreadsheet which I use to track the jobs we do every month. My manager has asked me to modify the sheet to track billable hours. The amount we can bill will vary depending on who is doing the work. There is already a box on the spreadsheet next to each job in which we record the person to whom that job is assigned. At the top of the spreadsheet Ive created a table, which I want to use to track who is working what billable hours. Ive now added another column so we can enter the number of hours worked on each job. Can I use IF statements to track who is working what hours? So for instance could I enter a formula into one of my tables cells that says something like this: €śLook at cells B5 to B12. Every time you see the initials RM in one of these cells, look at the corresponding cell in row C. Add up all the values in these cells in column C.€ť I know its a complicated one. Any help much appreciated. Thanks Karl |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tracking billable hours
Hi Karl,
sumif(B5:B12,"=RM",C5:C12) HTH Simon Karl wrote: Hi, I have a spreadsheet which I use to track the jobs we do every month. My manager has asked me to modify the sheet to track billable hours. The amount we can bill will vary depending on who is doing the work. There is already a box on the spreadsheet next to each job in which we record the person to whom that job is assigned. At the top of the spreadsheet Ive created a table, which I want to use to track who is working what billable hours. Ive now added another column so we can enter the number of hours worked on each job. Can I use IF statements to track who is working what hours? So for instance could I enter a formula into one of my tables cells that says something like this: €śLook at cells B5 to B12. Every time you see the initials RM in one of these cells, look at the corresponding cell in row C. Add up all the values in these cells in column C.€ť I know its a complicated one. Any help much appreciated. Thanks Karl -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tracking billable hours
Have a look at the SUMIF function i.e. =SUMIF(B5:B12,"RM",C5:C12)
"Karl" wrote: Hi, I have a spreadsheet which I use to track the jobs we do every month. My manager has asked me to modify the sheet to track billable hours. The amount we can bill will vary depending on who is doing the work. There is already a box on the spreadsheet next to each job in which we record the person to whom that job is assigned. At the top of the spreadsheet Ive created a table, which I want to use to track who is working what billable hours. Ive now added another column so we can enter the number of hours worked on each job. Can I use IF statements to track who is working what hours? So for instance could I enter a formula into one of my tables cells that says something like this: €śLook at cells B5 to B12. Every time you see the initials RM in one of these cells, look at the corresponding cell in row C. Add up all the values in these cells in column C.€ť I know its a complicated one. Any help much appreciated. Thanks Karl |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tracking billable hours
I have a spreadsheet which I use to track the jobs we do every month.
My manager has asked me to modify the sheet to track billable hours. The amount we can bill will vary depending on who is doing the work. There is already a box on the spreadsheet next to each job in which we record the person to whom that job is assigned. At the top of the spreadsheet I'e created a table, which I want to use to track who is working what billable hours. I've now added another column so we can enter the number of hours worked on each job. Can I use IF statements to track who is working what hours? So for instance could I enter a formula into one of my table's cells that says something like this: 'Look at cells B5 to B12. Every time you see the initials RM in one of these cells, look at the corresponding cell in row C. Add up all the values in these cells in column C.' Looks like a job for SUMIF. An example is in the CSV file below. A different approach is a pivot table report. It's described in Excel's built-in Help. ----------------------------- cut here ----------------------------- ,,,RM,"=SUMIF(B:C,D1,C:C)" ,,,BV,"=SUMIF(B:C,D2,C:C)" ,,,DA,"=SUMIF(B:C,D3,C:C)" 1,RM,1,, 2,BV,3,, 3,BV,1,, 4,RM,5,, 5,DA,2,, 6,BV,7,, 7,DA,2,, 8,DA,9,, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date and Time Response- before/during/after Business hours | Excel Discussion (Misc queries) | |||
How to seperate OT and Reg. Hours from total hours | Excel Worksheet Functions | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
Tracking Hours and Minutes | Excel Discussion (Misc queries) | |||
Subtracting paid hours from unpaid hours | Excel Worksheet Functions |