+ Reply to Thread
Results 1 to 5 of 5

Trying to calculate sum of timesheets based on the employee, timesheet type and the date

  1. #1
    Registered User
    Join Date
    08-25-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Trying to calculate sum of timesheets based on the employee, timesheet type and the date

    Hi,

    I am trying to put together a table that works out how many hours were submitted by each employee. Each timesheet has 3 types (Issue, Change Request, Support) and I want to break down submissions by each week of the year (see attached example with sample data).

    The raw data contains the employee's name (text) the date the timesheet (date) was submitted and it's type (text).

    I've been trying to use a SUMIFS calculation but i'm failing to even pick up anything when using the following formula for just employee and timesheet type, ignoring the date range:

    =SUMIFS(Timesheets!$H$2:$H$999,Timesheets!$D$2:$D$999,A12,Timesheets!$I$2:Timesheets!$I$999,C14)

    The criteria1 refers to a cell that is using a lookup: =LOOKUP(A13,Values!$A$3:$A$14,Values!$B$3:$B$14)
    The criteria2 refers to a text cell "Issue"

    This doesn't seem to be working and I can't figure out why... Haven't even got onto trying to split them by date.

    Help most appreciated!!!!

    Thanks
    Attached Files Attached Files
    Last edited by Archimede5; 08-25-2016 at 11:53 AM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Trying to calculate sum of timesheets based on the employee, timesheet type and the da

    You get better result on your question if you add a small excel file, without confidential information.

    Please also add the expected results manualy in your file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    08-25-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Re: Trying to calculate sum of timesheets based on the employee, timesheet type and the da

    Appreciated Have included table with sample data

  4. #4
    Registered User
    Join Date
    08-25-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Re: Trying to calculate sum of timesheets based on the employee, timesheet type and the da

    OK - so somehow by creating the sample data it's now picking up the two criteria as expected so please ignore my initial problem...

    The question now is how to add a third criteria using weeks of the year?

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Trying to calculate sum of timesheets based on the employee, timesheet type and the da

    with a pivot table.

    see the attached file.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate employee utilization based on billed hours
    By simrag01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-11-2014, 04:48 PM
  2. Formula to calculate # of hours based on date range and employee ID code
    By r0man3 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-02-2014, 08:04 PM
  3. [SOLVED] VBA Code to calculate utilization of an Employee using Start & End Date
    By nickydharia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2014, 10:30 AM
  4. Replies: 0
    Last Post: 06-04-2013, 09:01 PM
  5. [SOLVED] Need to aggregate employee timesheets into a summary sheet
    By cfell in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-23-2013, 02:03 PM
  6. Replies: 2
    Last Post: 05-15-2009, 02:42 PM
  7. How do I calculate an employee timesheet in Excel?
    By Raven in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-03-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1