The ExcelHelpDesk Support team received the following request for help
Problem – Manual Calculation of Staff Effort on Products
I have an excel file that is manually updated and calculated that marks what product our software developers have worked on during the day. This file, has different worksheets for each month (so each file has 12 worksheets as each one is represented by each month), and within each month
– is a list of staff names going vertically in the first column
– each day of that specific month going horizontally in the first row
– on each day we mark a reference code that enables us to determine which product a certain staff member worked on which
Then at the end of every financial year we can determine the overall cost of each product for the year, but this is manual calculation by going through each worksheet and marking the count. Somehow, we would like to automate the calculation of the amount of times a staff member works on a product for the past year.
I am struggling with which function I should be looking at? its been a while since I have done smething this complicated in excel and my knowledge isnt where it used to be. Hopefully some help is provided, it would be appreciated
If a copy of the file needs to be provided I can provide one with dummy information thanks
For this problem we received a copy of the workbook. We then used a formula to count the number of ‘Product’ occurences for each staff member. That count was then transferred to a staff member summary worksheet to report the total counts for all ‘Products’ across the team.
To simplify the formula and the structure of the workbook we also recommended to record all the staff time into one worksheet for the year, rather then a separate worksheet for each month.
Template – Workbook with recorded Staff activity
Below is a screen shot of the entry worksheet that is being used. Here the Staff Members are listed down the rows in the worksheet and the days are listed across the columns in the worksheet.
To allow for all data on a single worksheet and a simplified entry and formula creation we suggested that the Staff Members should be listed across the columns in the worksheet and the days to be listed down the rows in the worksheet. As a result the worksheet that will be used to capture the effort information looked like this.
Step 1 – Create Summary Worksheet to hold Totals
Now a Summary worksheet is needed to hold the totals for each Product and Staff Member in a simple table. See a screen shot of the Summary worksheet below, it lists the Staff Members down the rows of the worksheet and the Products across the columns of the worksheet.
Step 2 – Create formula to Lookup and Count Products
Now to calcluate the number of times a ‘Product’ is listed for a staff member in the effort template we will use the ‘COUNTIF’ function. The ‘COUNTIF’ function is used by comparing the values available on the Summary worksheet. For this case starting with the cell B4 we create the following formula
Where ‘EffortData’ is the worksheet containing the “Product” entries for each staff member for the full year. The column D:D is the column for the staff member 1. The criteria is to match on the value in Summary!B$2, which in the case is ‘Product1’. Once the entry for the first product is created the formula can be dragged across for all products and other counted values.
Step 3 – Complete for remaining Staff Members
Then for each staff member the first ‘COUNTIF’ formula needs to be created in each row. The formula cannot be dragged down as it has an absolute reference to the ‘EffortData’ worksheet. Simply copy or enter the formula for each staff member making sure that the reference in the ‘EffortData’ worksheet is correct for the staff member row being calculated.
Once that is done the Summary worksheet will hold totals for all counts across the ‘Products’ and ‘Staff Members’.
Finished – Sample File with COUNTIF formula
Click on the following line to access a sample file that includes the ‘EffortData’ and ‘Summary’ worksheets. The ‘COUNTIF’ formula is also included.
|Download Sample Lookup Timesheet|
Note: A more elegant solution to this problem could be provided through a series of macro’s and forms. This could also involve direct entry by staff into standalone worksheets that would consolidate into the central company storage.
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment