summing one range based on another range

[Sh0t2bts]

Hi All,

I am trying to sum a range of times based on another range.
I have three worksheets

The First has the following
A B C D
1 27/07/2004
2 Name Initials Tasks Task_Time
3 Jon P JP
4 Terry C TC

The Second Sheets has a list of the Tasks and the amount of time it should
take to complete:-
A B C
1 Task Task Time
No Name
2 1 Load Pallet 01:00:00
3 2 Stack Shelvs 00:15:00
4 3 Load Wagon 00:45:00

And Fanially the third sheet has a list of the task numbers completed the
initialls of who completed the taks and the date completed
A B C
1 Task Initials Date
No
2 1 JP 27/07/2004
3 1 JP 27/07/2004
4 2 JP 27/07/2004
5 3 TC 27/07/2004
6 1 TC 27/07/2004
7 1 TC 27/07/2004
8 3 JP 27/07/2004

In C3 on my first sheet I am using the following forula to calculate the
number of tasks carried out:-
=SUMPRODUCT(('Sheet2'!$B$2:$B$16=B3)*('Sheet2'!$C$2:$C$16=$A$1))
Now what I want to do is in D3 on sheet 1 is to sum the the time in sheet3
where the task number in sheet 2 Colum A = the task number in sheet 3 column
A and the initials in sheet1 column B = the initials in sheets 2 Column A


To round it up I want to count the number of tasks completed by each person
and sum the time it should have taken to complete these tasks.
I hope this makes sence?

Many Thanks
Mark

 

[Frank Kabel]

Hi
hope I got it right
try (I think you have messed a little bit with the sheet references.
Your formula in C3 should be:
=SUMPRODUCT(('Sheet3'!$B$2:$B$16=B3)*('Sheet3'!$C$2:$C$16=$A$1))

Now the easiest thing would be to double the time on sheet 3 with
=VLOOKUP(A2,'sheet2'!$A$2:$C$16,3,0)
in cell D2 and copy down

After this use the following formula in sheet1: D3:
=SUMPRODUCT(('Sheet3'!$B$2:$B$16=B3)*('Sheet3'!$C$2:$C$16=$A$1)*('Sheet
3'!$D$2:$D))
Format this cell with the custom format
[hh]:mm

 

[Domenic]

Hi,

D2: Remove the current label and enter 1, representing Task No. 1

E2: Enter the number 2

F2: Enter the number 3

D3, copied across and down:

=SUMPRODUCT(--(Sheet3!$A$2:$A$16=Sheet1!D$2),--(Sheet3!$B$2:$B$16=Sheet1!
$B3),--(Sheet3!$C$2:$C$16=Sheet1!$A$1))*VLOOKUP(D$2,Sheet2!$A$2:$C$4,3,0)

and format cells as "Time"

Hope this helps!

 

[Domenic]

Here's an alternative solution to the one I offered previously, which
doesn't require the addition of additional columns:

Tasks remains the same...

C3, copied down:

=SUMPRODUCT(--(Sheet3!$B$2:$B$16=$B3),--(Sheet3!$C$2:$C$16=$A$1))

Task Time...

D3, copied down:

=SUMPRODUCT((Sheet3!$B$2:$B$8=$B3)*(Sheet3!$C$2:$C$8=Sheet1!$A$1),LOOKUP(
Sheet3!$A$2:$A$8,Sheet2!$A$2:$C$4))

format as "Time"