Sumif with multiple lookup values

[jcliquidtension]

Hi,

I have a spreadsheet where I enter hours worked for employees. Some
employees work more than one department. After entering the hours and
departments, I have a pivot table that summarizes everything by (1) employee,
and (2) department. This works well.

Next, I have a data file that I use to import the info into ADP's PayExpert
payroll software. The data file uses sumif formulas to summarize each
employee's hours. It sums hours based on employee number. The problem is, I
need to figure out how to summarize the hours for the multiple department
employees by each department for each employee. For example, below is what
it's doing now:

Employee Temp. Dept. Hours O/T
Hours Mileage
100 23
$12.00
101 40
3.5 $0
102 35
$0
104 32
$9.60

In the above example, assuming employees 100 and 104 worked multiple
departments, what I need to do is have the hours and mileage summarized for
each department worked. Example:

Employee Temp. Dept. Hours O/T
Hours Mileage
100 15
$6.00
100 412623 8
$6.00
101 40
3.5 $0
102 35
$0
104 16
$2.80
104 416206 8
$2.80
104 416225 8
$4.00

Some other things to consider: Not all multiple department employees work
the same departments each week. There are more than 50 departments, and
almost every employee can possibly work at least 8-10 of 30 departments each
week.

Is this possible?

Thanks in advance!

 

[Bob Phillips]

How would you know that 23 breaks down to 15 for one, 8 for another, and
which they are?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jcliquidtension]

Hi Bob,

Thanks for replying. To answer your question, I input the hours from
handwritten timesheets in the following format:

Date Emp # Dept. Time In Time Out
3-23-07 100 412663 8:00 a.m. 10:00 a.m.
3-23-07 100 412623 10:00 a.m. 11:30 a.m.
and so on. The spreadsheet automatically calculates the hours, and the
mileage (if applicable) for each department. These totals are then displayed
in a pivot table for my own records. The data file (CSV) that I create,
however, has to be in a specific format to be imported into ADP's PayExpert.
So, where now I have sumif formulas that total the hours and mileage by
employee numbers, I need to split it by employee numbers, and within each
employee, the departments worked.

Thanks,
Jason