Simple IF or SUMIF?? Stuck!!

  • Thread starter Thread starter Rick Melton
  • Start date Start date
R

Rick Melton

I didn't explain myself very well!!

Col D is a rate column, 0=single time and 2=double time.
All the numbers in col B are staff numbers & col E is the number of hours
they have worked.
What I want to do is add up all the single time hours & double time hours a
member
of staff has worked separately.
So staff No. 3456 has done a total of 16.50 hours at rate 0 and 7.25 hours
at rate 2.
I'm trying to get excel to display these two values in a different worksheet

col B col D colE
3456 2 3.25
7654 0 5.25
3456 2 2.00
3456 0 16.50
7654 0 23.00
3456 2 2.00
7654 0 17.75
7654 2 1.25
4321 2 43.50
4321 0 32.00

Thanx Ron, but couldn't get the array thing to work!!
I think there is a SUMIF or IF statement that will do it - I just can't
figure it out!!

Any help would really be appreciated
Thanx in advance
Rick
 
Rick,

Not sure what bob posted, but . . .
with your data starting in row 1 on sheet1
=SUMPRODUCT((Sheet1!B1:B10=3456)*(Sheet1!D1:D10=0)*(Sheet1!E1:E10))
will give regular hours for employee 3456
=SUMPRODUCT((Sheet1!B1:B10=3456)*(Sheet1!D1:D10=2)*(Sheet1!E1:E10))
will give double time hours for employee 3456

Dan E
 
I mean, not sure what RON posted . . .

Dan E

Dan E said:
Rick,

Not sure what bob posted, but . . .
with your data starting in row 1 on sheet1
=SUMPRODUCT((Sheet1!B1:B10=3456)*(Sheet1!D1:D10=0)*(Sheet1!E1:E10))
will give regular hours for employee 3456
=SUMPRODUCT((Sheet1!B1:B10=3456)*(Sheet1!D1:D10=2)*(Sheet1!E1:E10))
will give double time hours for employee 3456

Dan E
Click to expand...
 
Back
Top