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

1. ## 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

2. ## 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.

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. ## 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. ## Re: Trying to calculate sum of timesheets based on the employee, timesheet type and the da

with a pivot table.

see the attached file.

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